全部产品
云市场

数据写入Table Store

更新时间:2019-05-24 13:37:50

本文以在DLA中,读取OSS数据(customer、nation表),并通过INSERT…SELECT将符合条件的数据写入Table Store为例,介绍如何通过DLA向Table Store中写入数据。

操作步骤

步骤一:创建OSS Schema

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

步骤二:创建OSS表

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://${您的bucket}/datasets/tpch/50x/text_date/customer_text'

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://${您的bucket}/datasets/tpch/50x/text_date/nation_text'

步骤三:创建Table Store Schema

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

步骤四:创建Table Store表test_insert

  1. CREATE EXTERNAL TABLE test_insert (
  2. id1_int int NOT NULL COMMENT '客户id主键',
  3. c_address varchar(20) NULL COMMENT '客户的地址',
  4. c_acctbal double NULL COMMENT '客户的account balance',
  5. PRIMARY KEY (`id1_int`)
  6. )

步骤五:查询数据

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

查询nation表数据,其中CANADA的nationkey是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)

查询customer表数据,查询条件为nationkey=3以及c_mktsegment=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)

步骤六:导入数据

找到n_name为CANADA、c_mktsegment为BUILDING的客户,然后对c_custkey降序排序,选择前10条数据,将其对应的c_custkey、c_address、c_acctbal三列导入到Table Store的test_insert表中。

先查询数据,确认有几条数据

  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)

开始导入数据

  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)

步骤七:验证结果

  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)