All Products
Search
Document Center

Analyze data in Table Store

Last Updated: Jul 25, 2019

Data Lake Analytics (DLA) is an interactive analytics service that utilizes serverless architecture and does not require ETL processes. With DLA, you can query and analyze data stored in Table Store by connecting to Table Store through the standard Java Database Connectivity (JDBC) API.

This topic explains how to use DLA to quickly analyze data in Table Store.

Comparisons between types of Table Store and DLA fields

  • Mapping of concepts such as of databases and tables

    Table Store concept DLA concept
    Instance Schema or database
    Table Table
    PK column column, isPrimaryKey=true, isNullable=false.
    Non-PK column column, isPrimaryKey=false, isNullable=<defined through DDL >.
  • Field mapping

    Table Store DLA
    INTEGER(8 bytes) bigint(8 bytes)
    STRING varchar
    BINARY varbinary
    DOUBLE double
    BOOLEAN boolean

Prerequisites

Before using DLA to analyze data in Table Store, you must first prepare Table Store test data. To do so, follow these steps:

  1. Sign up for Table Store

  2. Create an instance

  3. Create a data table

  4. Bind a VPC

  5. Write data to the table

In the following example, a nation table is created in Table Store with 25 records inserted into it.

Table store test data

Read Table Store data using DLA

To read data in Table Store with DLA, follow these steps:

1. Connect to Table Store and create a DLA database.

Log on to the DLA console, click Log on to DMS console, and create a connection to Table Store. The connection syntax is as follows:

  1. CREATE SCHEMA hangzhou_ots_test WITH DBPROPERTIES (
  2. catalog = 'ots',
  3. location = 'https://otsInstanceName.cn-hangzhou.ots-internal.aliyuncs.com',
  4. instance = 'ots-instance-name'
  5. );

DLA and Table Store use VPC-related policies to open the private network environment for you. This means that you must use the Table Store private network address when creating a connection.

  • catalog = 'ots': The connection to Table Store with DLA.

  • hangzhou_ots_test: The name of the DLA database.

  • location: Indicates the DLA Access Address or Private Address in your OST instance, as shown in the following figure. These two addresses are the same.

  • instance: The name of the Table Store instance.

2. View the created database and table in DLA.

After creating a DLA database, you can view it and the Table Store nation table.

  • View database using the MySQL command-line tool

    1. mysql> show databases;
    2. +------------------------------+
    3. | Database |
    4. +------------------------------+
    5. | hangzhou_ots_test |
    6. +------------------------------+
    7. 1 rows in set (0.22 sec)
    8. mysql> show create database hangzhou_ots_test;
    9. +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    10. | Database | Create Database |
    11. +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    12. | hangzhou_ots_test | CREATE DATABASE `hangzhou_ots_test`
    13. WITH DBPROPERTIES (
    14. CATALOG = 'ots',
    15. location = 'https://otsInstanceName.cn-hangzhou.ots-internal.aliyuncs.com',
    16. instance = 'ots-instance-name'
    17. ) |
    18. +-------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    19. 1 row in set (0.31 sec)
  • View table using the MySQL command-line tool

    1. mysql> use hangzhou_ots_test;
    2. Database changed
    3. mysql> show tables;
    4. Empty set (0.30 sec)

3. Create a Table Store mapping table in DLA and synchronize Table Store table data.

After you create a DLA table, DLA automatically synchronizes Table Store table data to the DLA table.

  1. mysql> CREATE EXTERNAL TABLE `nation` (
  2. `N_NATIONKEY` bigint not NULL ,
  3. `N_COMMENT` varchar(100) NULL ,
  4. `N_NAME` varchar(100) NULL ,
  5. `N_REGIONKEY` varchar NULL ,
  6. PRIMARY KEY (`N_NATIONKEY`)
  7. );
  8. Query OK, 0 rows affected (0.36 sec)

The preceding fields must be identical to the primary key field order and name in Table Store. For more information about fields, see Comparisons between types of Table Store and DLA fields.

4. Query Table Store data in DLA

After the data in Table Store is synchronized to DLA, you can query it using standard MySQL syntax.

  1. mysql> select count(*) from nation;
  2. +-------+
  3. | _col0 |
  4. +-------+
  5. | 25 |
  6. +-------+
  7. 1 row in set (1.19 sec)
  8. mysql> select * from nation;
  9. +-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
  10. | n_nationkey | n_comment | n_name | n_regionkey |
  11. +-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
  12. | 0 | haggle. carefully final deposits detect slyly agai | ALGERIA | 0 |
  13. | 1 | al foxes promise slyly according to the regular accounts. bold requests alon | ARGENTINA | 1 |
  14. | 2 | y alongside of the pending deposits. carefully special packages are about the ironic forges. slyly special | BRAZIL | 1 |
  15. | 3 | eas hang ironic, silent packages. slyly regular packages are furiously over the tithes. fluffily bold | CANADA | 1 |
  16. | 4 | y above the carefully unusual theodolites. final dugouts are quickly across the furiously regular d | EGYPT | 4 |
  17. | 5 | ven packages wake quickly. regu | ETHIOPIA | 0 |
  18. | 6 | refully final requests. regular, ironi | FRANCE | 3 |
  19. | 7 | l platelets. regular accounts x-ray: unusual, regular acco | GERMANY | 3 |
  20. | 8 | ss excuses cajole slyly across the packages. deposits print aroun | INDIA | 2 |
  21. | 9 | slyly express asymptotes. regular deposits haggle slyly. carefully ironic hockey players sleep blithely. carefull | INDONESIA | 2 |
  22. | 10 | efully alongside of the slyly final dependencies. | IRAN | 4 |
  23. | 11 | nic deposits boost atop the quickly final requests? quickly regula | IRAQ | 4 |
  24. | 12 | ously. final, express gifts cajole a | JAPAN | 2 |
  25. | 13 | ic deposits are blithely about the carefully regular pa | JORDAN | 4 |
  26. | 14 | pending excuses haggle furiously deposits. pending, express pinto beans wake fluffily past t | KENYA | 0 |
  27. | 15 | rns. blithely bold courts among the closely regular packages use furiously bold platelets? | MOROCCO | 0 |
  28. | 16 | s. ironic, unusual asymptotes wake blithely r | MOZAMBIQUE | 0 |
  29. | 17 | platelets. blithely pending dependencies use fluffily across the even pinto beans. carefully silent accoun | PERU | 1 |
  30. | 18 | c dependencies. furiously express notornis sleep slyly regular accounts. ideas sleep. depos | CHINA | 2 |
  31. | 19 | ular asymptotes are about the furious multipliers. express dependencies nag above the ironically ironic account | ROMANIA | 3 |
  32. | 20 | ts. silent requests haggle. closely express packages sleep across the blithely | SAUDI ARABIA | 4 |
  33. | 21 | hely enticingly express accounts. even, final | VIETNAM | 2 |
  34. | 22 | requests against the platelets use never according to the quickly regular pint | RUSSIA | 3 |
  35. | 23 | eans boost carefully special requests. accounts are. carefull | UNITED KINGDOM | 3 |
  36. | 24 | y final packages. slow foxes cajole quickly. quickly silent platelets breach ironic accounts. unusual pinto be | UNITED STATES | 1 |
  37. +-------------+--------------------------------------------------------------------------------------------------------------------+----------------+-------------+
  38. 25 rows in set (1.63 sec)

From the ID in the following figure, you can see the same data as in Table Store.

dla