Trafodion Bulk Load 初试

如在文章《Trafodion 数据加载介绍》中提到，Trafodion的Bulk Load与Tricke Load不同，Bulk Load主要是针对大数据量的且一般是批量装载的方式。Bulk Load通常使用LOAD语句实现。

下面介绍几种Bulk Load方式，

从Trafodion表加载数据从HDFS文件加载数据（Hive外部表）从Hive表加载数据从外部数据库加载数据（Mysql to Hive）

从Trafodion表加载数据

（1）创建两个表结构相同的测试表

SQL>showddl test_tbl; CREATE TABLE TRAFODION.SEABASE.TEST_TBL ( ID INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED , NAME VARCHAR(10) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED ) ; --- SQL operation complete. SQL>showddl test_tbl2; CREATE TABLE TRAFODION.SEABASE.TEST_TBL2 ( ID INT NO DEFAULT NOT NULL NOT DROPPABLE SERIALIZED , NAME VARCHAR(10) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED ) ; --- SQL operation complete. （2）表1有几条数据，表2没有数据

SQL>select * from test_tbl; ID NAME ----------- ---------- 1 LIU 2 zhang 3 LIU 4 wu 5 feng --- 5 row(s) selected. SQL>select * from test_tbl2; --- 0 row(s) selected. （3）从表1加载数据到表2

SQL>load into test_tbl2 select * from test_tbl; UTIL_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- Task: LOAD Status: Started Object: TRAFODION.SEABASE.TEST_TBL2 Task: CLEANUP Status: Started Object: TRAFODION.SEABASE.TEST_TBL2 Task: CLEANUP Status: Ended Object: TRAFODION.SEABASE.TEST_TBL2 Task: PREPARATION Status: Started Object: TRAFODION.SEABASE.TEST_TBL2 Rows Processed: 5 Task: PREPARATION Status: Ended ET: 00:00:00.263 Task: COMPLETION Status: Started Object: TRAFODION.SEABASE.TEST_TBL2 Task: COMPLETION Status: Ended ET: 00:00:00.531 --- SQL operation complete. SQL>select * from test_tbl2; ID NAME ----------- ---------- 1 LIU 2 zhang 3 LIU 4 wu 5 feng --- 5 row(s) selected.

从HDFS文件加载数据（Hive 外部表）

（1）创建测试文件并利用scp或者其他方式上传到Hadoop Cluster上，内容如下

[centos@cent-1 tmp]$ cat test_table Lucy,20,F Jason,22,M Stephen,21,M Lily,19,F Vincent,25,M

（2）将测试文件导入HDFS中

[hdfs@cent-1 ~]$ hadoop fs -mkdir -p /hive/test/test_tbl [hdfs@cent-1 ~]$ hadoop fs -copyFromLocal /tmp/test_table /hive/test/test_tbl [hdfs@cent-1 ~]$ hadoop fs -ls /hive/test/test_tbl Found 1 items -rw-r--r--   3 hdfs supergroup         57 2016-09-28 02:31 /hive/test/test_tbl/test_table

（3）在Trafodion中创建测试表

SQL>create table test_table ( name varchar(10), age int, sex varchar(1)); --- SQL operation complete. SQL>showddl test_table; CREATE TABLE TRAFODION.SEABASE.TEST_TABLE ( NAME VARCHAR(10) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED , AGE INT DEFAULT NULL SERIALIZED , SEX VARCHAR(1) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED ) ; --- SQL operation complete.

（4）在Hive中创建Hive外部表，指向前面对应的HDFS文件

[hdfs@cent-1 ~]$ hive Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.4.8-1.cdh5.4.8.p0.4/jars/hive-common-1.1.0-cdh5.4.8.jar!/hive-log4j.properties WARNING: Hive CLI is deprecated and migration to Beeline is recommended. hive> create external table test_tbl (name string,age int,sex string) row format delimited fields terminated by ',' location '/hive/test/test_tbl'; OK Time taken: 0.085 seconds hive> show tables; OK test_tbl Time taken: 0.364 seconds, Fetched: 1 row(s)

（5）加载数据到Trafodion表

SQL>load into test_table +>select * from hive.hive.test_tbl; UTIL_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- Task: LOAD Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Task: CLEANUP Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Task: CLEANUP Status: Ended Object: TRAFODION.SEABASE.TEST_TABLE Task: PREPARATION Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Rows Processed: 5 Task: PREPARATION Status: Ended ET: 00:00:01.272 Task: COMPLETION Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Task: COMPLETION Status: Ended ET: 00:00:00.254 --- SQL operation complete. SQL>select * from test_table; NAME AGE SEX ---------- ----------- ---- Lucy 20 F Jason 22 M Stephen 21 M Lily 19 F Vincent 25 M --- 5 row(s) selected.

从Hive表加载数据

（1）在Hive中创建测试表并插入几条数据

hive> describe hive_tbl; OK name                    string age                     int sex                     string Time taken: 0.107 seconds, Fetched: 3 row(s) hive> select * from hive_tbl; OK Lucy 20 F Jason 22 M Stephen 21 M Time taken: 0.093 seconds, Fetched: 3 row(s)

（2）Trafodion中的表继续沿用上述测试表，先清空数据

SQL>purgedata test_table; --- SQL operation complete. SQL>showddl test_table; CREATE TABLE TRAFODION.SEABASE.TEST_TABLE ( NAME VARCHAR(10) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED , AGE INT DEFAULT NULL SERIALIZED , SEX VARCHAR(1) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT NULL SERIALIZED ) ; --- SQL operation complete. （3）从Hive加载数据到Trafodion

（注：在做从hive到trafodion数据加载的时候有几个参数可以配置，用来提升加载性能，如HIVE_MAX_STRING_LENGTH、ALLOW_INCOMPATIBLE_ASSIGNMENT

   典型的用法如下：

    CQD HIVE_MAX_STRING_LENGTH '1000'; --用于当列的最大长度为1KB时

    CQD ALLOW_INCOMPATIBLE_ASSIGNMENT 'ON'; --用于当有时间相关的列的时候）

SQL>load into test_table select * from hive.hive.hive_tbl; UTIL_OUTPUT -------------------------------------------------------------------------------------------------------------------------------- Task: LOAD Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Task: CLEANUP Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Task: CLEANUP Status: Ended Object: TRAFODION.SEABASE.TEST_TABLE Task: PREPARATION Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Rows Processed: 3 Task: PREPARATION Status: Ended ET: 00:00:00.390 Task: COMPLETION Status: Started Object: TRAFODION.SEABASE.TEST_TABLE Task: COMPLETION Status: Ended ET: 00:00:00.447 --- SQL operation complete. SQL>select * from test_table; NAME AGE SEX ---------- ----------- ---- Lucy 20 F Jason 22 M Stephen 21 M --- 3 row(s) selected.

从外部数据库加载数据（Mysql to Hive）

（1）在集群上安装并启动Sqoop，Sqoop安装步骤请参照其官方文档 点击打开链接

（2）下载mysql connector Jar包，并放到/var/lib/sqoop目录下

（3）在某台机器上安装Mysql，安装Mysql步骤此处不作详细说明

（4）在mysql数据中创建测试表，并插入几条数据

mysql> use test; Database changed mysql> create table test_tbl(name varchar(10), age int, sex char(1)); Query OK, 0 rows affected (0.00 sec) mysql> select * from test; ERROR 1146 (42S02): Table 'test.test' doesn't exist mysql> select * from test_tbl; Empty set (0.00 sec) mysql> insert into test_tbl values('Wang wei', 30, 'M'); Query OK, 1 row affected (0.00 sec) mysql> insert into test_tbl values('Li ping', 29, 'F'); Query OK, 1 row affected (0.00 sec) mysql> mysql> mysql> select * from test_tbl; +----------+------+------+ | name | age | sex | +----------+------+------+ | Wang wei | 30 | M | | Li ping | 29 | F | +----------+------+------+ 2 rows in set (0.00 sec)

（5）使用sqoop命令从mysql往hive导入数据

[hdfs@cent-2 ~]$ sqoop import --connect jdbc:mysql://localhost:3306/test --driver com.mysql.jdbc.Driver --username centos --table test_tbl --split-by name --hive-import --create-hive-table --hive-table test_tabl_hive Warning: /opt/cloudera/parcels/CDH-5.4.8-1.cdh5.4.8.p0.4/bin/../lib/sqoop/../accumulo does not exist! Accumulo imports will fail. ... Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.4.8-1.cdh5.4.8.p0.4/jars/hive-common-1.1.0-cdh5.4.8.jar!/hive-log4j.properties OK Time taken: 3.052 seconds Loading data to table default.test_tabl_hive Table default.test_tabl_hive stats: [numFiles=4, totalSize=27] OK Time taken: 0.748 seconds

（6）从Trafodion或Hive Shell中查看数据是否导入成功

SQL>select * from hive.hive.test_tabl_hive; NAME AGE SEX -------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------------------------------------------------------------------------------------------------------------- Li ping 29 F Wang wei 30 M --- 2 row(s) selected.