All Products
Search

This Product

    Data Lake Analytics - Deprecated:Insert data into Table Store

    Document Center

    Data Lake Analytics - Deprecated:Insert data into Table Store

    Last Updated:May 09, 2019

    This topic describes how to read data from OSS and then write the data to Table Store. In the following example, two OSS tables, customer and nation, are created. The INSERT…SELECT statement is used to read data from these two tables and then to write the results to Table Store.

    Procedure

    Step 1: Create an OSS schema

    1. CREATE DATABASE tpch_50x_text
    2. WITH DBPROPERTIES (
    3. catalog = 'oss',
    4. location = 'oss://${your bucket}/datasets/tpch/50x/text_date/'
    5. )

    Step 2: Create OSS tables

    Table customer

    1. CREATE EXTERNAL TABLE tpch_50x_text.customer (
    2. c_custkey int,
    3. c_name string,
    4. c_address string,
    5. c_nationkey int,
    6. c_phone string,
    7. c_acctbal double,
    8. c_mktsegment string,
    9. c_comment string
    10. )
    11. ROW FORMAT DELIMITED
    12. FIELDS TERMINATED BY '|'
    13. STORED AS TEXTFILE
    14. LOCATION 'oss://${your bucket}/datasets/tpch/50x/text_date/customer_text'

    Table nation

    1. CREATE EXTERNAL TABLE tpch_50x_text.nation (
    2. n_nationkey int,
    3. n_name string,
    4. n_regionkey int,
    5. n_comment string
    6. )
    7. ROW FORMAT DELIMITED
    8. FIELDS TERMINATED BY '|'
    9. STORED AS TEXTFILE
    10. LOCATION 'oss://${your bucket}/datasets/tpch/50x/text_date/nation_text'

    Step 3: Create a Table Store schema

    1. CREATE DATABASE etl_ots_test
    2. WITH DBPROPERTIES (
    3. catalog = 'ots',
    4. location = 'https://${your Table Store instance}.cn-shanghai.ots-internal.aliyuncs.com',
    5. instance = '${your Table Store instance}'

    Step 4: Create a Table Store table named test_insert

    1. CREATE EXTERNAL TABLE test_insert (
    2. id1_int int NOT NULL COMMENT 'Customer ID primary key',
    3. c_address varchar(20) NULL COMMENT 'Customer address',
    4. c_acctbal double NULL COMMENT 'Customer account balance',
    5. PRIMARY KEY (`id1_int`)
    6. )

    Step 5: Query data in the tables

    1. mysql> select * from etl_ots_test.test_insert;
    2. Empty set (0.31 sec)

    Query data in the nation table, where the nationkey of CANADA is 3:

    1. mysql> select n_nationkey, n_name from nation;
    2. +-------------+----------------+
    3. | n_nationkey | n_name         |
    4. +-------------+----------------+
    5. |           0 | ALGERIA        |
    6. |           1 | ARGENTINA      |
    7. |           2 | BRAZIL         |
    8. |           3 | CANADA         |
    9. |           4 | EGYPT          |
    10. |           5 | ETHIOPIA       |
    11. |           6 | FRANCE         |
    12. |           7 | GERMANY        |
    13. |           8 | INDIA          |
    14. |           9 | INDONESIA      |
    15. |          10 | IRAN           |
    16. |          11 | IRAQ           |
    17. |          14 | KENYA          |
    18. |          15 | MOROCCO        |
    19. |          16 | MOZAMBIQUE     |
    20. |          17 | PERU           |
    21. |          18 | CHINA          |
    22. |          19 | ROMANIA        |
    23. |          20 | SAUDI ARABIA   |
    24. |          21 | VIETNAM        |
    25. |          22 | RUSSIA         |
    26. |          23 | UNITED KINGDOM |
    27. |          24 | UNITED STATES  |
    28. +-------------+----------------+
    29. 25 rows in set (0.37 sec)

    Query data in the customer table based on the nationkey of 3 and c_mktsegment of BUILDING:

    1. mysql> select count(*) from customer where c_nationkey = 3 and c_mktsegment = 'BUILDING';
    2. +----------+
    3. | count(*) |
    4. +----------+
    5. |    60350 |
    6. +----------+
    7. 1 row in set (0.66 sec)
    1. mysql> select * from customer where c_nationkey = 3 and c_mktsegment = 'BUILDING' order by c_custkey limit 3;
    2.     +-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
    3. | c_custkey | c_name             | c_address               | c_nationkey | c_phone         | c_acctbal | c_mktsegment | c_comment                                                                                          |
    4. +-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
    5. |        13 | Customer#000000013 | nsXQu0oVjD7PM659uC3SRSp |           3 | 13-761-547-5974 |   3857.34 | BUILDING     | ounts sleep carefully after the close frays. carefully bold notornis use ironic requests. blithely |
    6. |        27 | Customer#000000027 | IS8GIyxpBrLpMT0u7       |           3 | 13-137-193-2709 |   5679.84 | BUILDING     |  about the carefully ironic pinto beans. accoun                                                    |
    7. |        40 | Customer#000000040 | gOnGWAyhSV1ofv          |           3 | 13-652-915-8939 |    1335.3 | BUILDING     | rges impress after the slyly ironic courts. foxes are. blithely                                    |
    8. +-----------+--------------------+-------------------------+-------------+-----------------+-----------+--------------+----------------------------------------------------------------------------------------------------+
    9. 3 rows in set (0.78 sec)

    Step 6: Insert data

    Locate the customers whose n_name is CANADA and c_mktsegment is BUILDING, sort the records in descending order by c_custkey, and select the first 10 records. Insert the data in the c_custkey, c_address, and c_acctbal columns to the test_insert table in Table Store.

    First confirm the number of data records

    1. mysql> select c.c_custkey, c.c_address, c.c_acctbal 
    2. from tpch_50x_text.customer c
    3. join tpch_50x_text.nation n 
    4. on c.c_nationkey = n.n_nationkey
    5. where n.n_name = 'CANADA' 
    6. and c.c_mktsegment = 'BUILDING' 
    7. order by c.c_custkey
    8. limit 10;
    9. +-----------+--------------------------------+-----------+
    10. | c_custkey | c_address                      | c_acctbal |
    11. +-----------+--------------------------------+-----------+
    12. |        13 | nsXQu0oVjD7PM659uC3SRSp        |   3857.34 |
    13. |        27 | IS8GIyxpBrLpMT0u7              |   5679.84 |
    14. |        40 | gOnGWAyhSV1ofv                 |    1335.3 |
    15. |        64 | MbCeGY20kaKK3oalJD,OT          |   -646.64 |
    16. |       255 | I8Wz9sJBZTnEFG08lhcbfTZq3S     |   3196.07 |
    17. |       430 | s2yfPEGGOqHfgkVSs5Rs6 qh,SuVmR |   7905.17 |
    18. |       726 | 4w7DOLtN9Hy,xzZMR              |   6253.81 |
    19. |       905 | f iyVEgCU2lZZPCebx5bGp5        |   -600.73 |
    20. |      1312 | f5zgMB4MHLMSHaX0tDduHAmVd4     |    9459.5 |
    21. |      1358 | t23gsl4TdVXqTZha DioEHIq5w7y   |   5149.23 |
    22. +-----------+--------------------------------+-----------+
    23. 10 rows in set (1.09 sec)

    Insert data

    1. mysql> insert into etl_ots_test.test_insert (id1_int ,c_address, c_acctbal)
    2.     select c.c_custkey, c.c_address, c.c_acctbal 
    3.     from tpch_50x_text.customer c
    4.     join tpch_50x_text.nation n 
    5.     on c.c_nationkey = n.n_nationkey
    6.     where n.n_name = 'CANADA' 
    7.     and c.c_mktsegment = 'BUILDING' 
    8.     order by c.c_custkey
    9.     limit 10;
    10. +------+
    11. | rows |
    12. +------+
    13. |   10 |
    14. +------+
    15. 1 row in set (2.14 sec)

    Step 7: Verify the results

    1. mysql> select * from etl_ots_test.test_insert;
    2. +---------+--------------------------------+-----------+
    3. | id1_int | c_address                      | c_acctbal |
    4. +---------+--------------------------------+-----------+
    5. |      13 | nsXQu0oVjD7PM659uC3SRSp        |   3857.34 |
    6. |      27 | IS8GIyxpBrLpMT0u7              |   5679.84 |
    7. |      40 | gOnGWAyhSV1ofv                 |    1335.3 |
    8. |      64 | MbCeGY20kaKK3oalJD,OT          |   -646.64 |
    9. |     255 | I8Wz9sJBZTnEFG08lhcbfTZq3S     |   3196.07 |
    10. |     430 | s2yfPEGGOqHfgkVSs5Rs6 qh,SuVmR |   7905.17 |
    11. |     726 | 4w7DOLtN9Hy,xzZMR              |   6253.81 |
    12. |     905 | f iyVEgCU2lZZPCebx5bGp5        |   -600.73 |
    13. |    1312 | f5zgMB4MHLMSHaX0tDduHAmVd4     |    9459.5 |
    14. |    1358 | t23gsl4TdVXqTZha DioEHIq5w7y   |   5149.23 |
    15. +---------+--------------------------------+-----------+
    16. 10 rows in set (0.27 sec)