数据加载

选择合适的方法来添加行

你可以使用下面的SQL语句来添加行到Trafodion表中：

• INSERT

• UPSERT

• UPSERT USING LOAD

• LOAD

有关这些语句的格式，请参考 Trafodion SQL Reference Manual (pdf, 3.98 MB)。下面这张表格说明了这些语句间的区别，目的是为了帮助你针对给定的需求来选择合适的语句。

接下来这个表个列出了这三种SQL语句不同的数据源，以及每种数据源的相关优点，特别是针对速度方面。这里的目的仍旧是帮助你怎么决定哪种数据源最合适。你对数据源的选择通常会被你收到的需要被加载的数据的格式所影响。所有数据的摄取率将依赖于硬件、行的长度以及列的类型。数据表明现在这里有一套四节点的系统，每个节点有16核，128GB的内存以及一块正规的磁盘。行的长度是150字节，列的类型与TPC-H中的Lineitem表（表示在线商品的信息，译者注）类似。注意： 标记<TBD>单元的吞吐量会在将来被加上。

从其它RDBMS中导入数据

从其它的RDBMS或外部数据源向Trafodion集群中导入大量的重要数据，可以通过下面两步完美实现：

1. 在Trafodion集群中，将数据从源头导入进Hive的表。请使用下面方法中的任意一个：
   • 在Trafodion系统中，使用一个类似Apache Sqoop（TM）的工具，来将数据从某个关系型数据库管理系统，就像MySQL或Oracle，迁移到Hive表中。更多信息，请参考使用Sqoop将数据导入进Hive
   • 在Trafodion集群中，将需要被导入进Trafodion中的数据复制到HDFS中。使用Hive外部表来让这些数据能从Hive中看到。更多信息，请参考创建Hive外部表
2. 使用Trafodion的LOAD语句来将数据从Hive导入进Trafodion表中。更多信息，请参考Bulk加载数据进Trafodion表

如果你想要从某个支持ODBC的数据库中迁移相对少量（只是几百万行）的数据，那么可以使用odb工具，它具有并行数据加载与抓取的能力。更多信息，请参考Trickle加载数据进Trafodion表。你还可以使用ETL工具，像SQuirrel-SQL， Pentaho或者Informatica。这些工具会通过ODBC或者JDBC连接上Trafodion数据库，然后根据合适的行集合大小来递交INSERT语句。预期它们比odb拥有更小的吞吐量。

使用Sqoop将数据导入进Hive

使用像Apache Sqoop(TM)这样的工具，在Trafodion系统中，将数据从远程关系型数据库管理系统（RDBMS），就像MySQL或Oracle中，导入进Hive表。

安装必需软件

默认情况下， Sqoop并没有被安装在Trafodion集群中。可以通过Ambari或者Cloudera Manager GUI在Trafodion集群中安装以及启动Sqoop。请参考Sqoop安装指南。 关于怎么使用Sqoop，请参考Sqoop用户手册.

在Trafodion集群中安装JDK 1.8以及Oracle JDBC驱动。你使用它们，仅仅为了从RDBMS中导入数据到Hive表里。请设置下面这些环境变量：

export JAVA_HOME=/opt/java/jdk1.8.0_11
export JAVA_OPTIONS=-Dmapred.child.java.opts=\-Djava.security.egd=file:/dev/urandom+

Sqoop命令的范例

列出所有Oracle表

sqoop list-tables --driver oracle.jdbc.OracleDriver --connect jdbc:oracle:thin:@<Oracle host name>:<port>/<database> 
--username <user-name> --password <password>

导入数据进Hive

sqoop import --connect jdbc:oracle:thin:@<Oracle host name:port>/<database> --username <user-name> 
--password <password> --table <tablename> --split-by <column-name> --hive-import --create-hive-table 
--hive-table <hive-table-name> --hive-overwrite --null-string '' --null-non-string '' 
--hive-drop-import-delims --verbose

创建Hive外部表

一张Hive表必须从一个Hive界面，像Hive shell中创建。在加载的时候使用Hive的外部表会非常方便。你可以通过复制源数据文件到单一HDFS文件夹下，并建立一张Hive外部表指向该文件夹，来将数据轻松导入进Hive表中。Hive表的每个列的数据类型必须与源数据一致。有关建立外部表的格式，请参考Hive wiki。有关Hive表可用的数据类型，请参考Hive语言手册与类型。Hive表可以通过Trafodion的整型、字符串型以及字符型的列进入访问。想要填充一张外部表，请使用这样类型的hadoop命令来将数据复制到HDFS文件夹下：

hadoop fs -copyFromLocal <src-file> <target-hdfs-file>

新建Trafodion表与索引

通过使用带有SALT USING <num> PARTITIONS的子句（盐粒分布）与HBASE_OPTIONS子句（压缩以及加密）的CREATE TABLE语句来新建Trafodion表。例如：

create table trafodion.sch.demo
(
demo_sk int not null,
name varchar(100),
primary key (demo_sk)
)
hbase_options
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'SNAPPY',
MEMSTORE_FLUSH_SIZE = '1073741824'
)
salt using 8 partitions on (demo_sk);

表中任何的索引都可能被盐粒分布（salted）或者未被这样，如果被盐粒分布了，那么盐粒分布的键与分区数必须与表一致。

create index demo_ix on sch.demo(name)
hbase_options
(
DATA_BLOCK_ENCODING = 'FAST_DIFF',
COMPRESSION = 'GZ'
)
salt like table;

选择一个主键

Trafodion表的主键必须根据访问这张表的工作量来选择。因为HBase是键值（key-value）存储，所以通过键来访问Trafodion表非常有效率。当你知道一些语句在断言（predicate）与连接（join）条件之外来访问一张表，你可以选择一个主键，并且其中的头键列有很高的选择断言。这会限制在HBase中需要被扫描的行数。当断言只出现在边键列而非头键列中时，Trafodion使用MDAM（Multi Dimensional Access Method）来限制被扫描的行数。当头键列（没有断言出现）的唯一条目数（unique entry count）很低时，MDAM能很好工作。

盐粒分布一张表

Trafodion表能被盐粒分布用以避免热污点（hot-spotting）。在一些工作量下，有了排列分区数据，数据的某种键的排列能比另一些排列有更多对表的访问量。这会导致在HBase RegionServers处理大多数加载时，出现不平衡的使用行为。对于本机HBase表，经常可以通过设计合适的键来处理。在Trafodion，一旦你选择了表的键，正如选择一个主键中所讨论的一样，你可以使用盐粒分布（salting）来均匀分布数据。盐粒分布对salt键使用一个哈希函数并且根据该哈希的值来将数据分布到分区中。这个哈希的值被物理存储在表中，作为头键值。表的每次拆分只会有一个salt键值。salting键可以是主键的任意子集（包括主键本身）。保持salting键尽可能小，这是一个很好的实践。这个键必须提供数据平均分配。这在当键值有很大的唯一条目数（unique entry count）并没有严重倾斜的时候，能够实现。在创建表的时候，分区数也应该被明确。这依赖于集群（cluster）的大小，以及表的期望大小。一张盐粒分布的表能被拆分，如果超出预期的数据被添加进该表。这会导致超出一个分区有着同样salt值的行，并且也可能产生基于该表的次最优执行计划。

你可同样可以选择不盐粒分布Trafodion的表，这就与传统RDBMS排列分区类似。分区数会随着表的增大而增多，并且排列边界会被HBase所决定，而HBase又基于指定的拆分策略。

使用压缩与加密

大的Trafodion表必须被加密或者压缩。因为HBase在一行里为每列单独存储键，所以再相同数据下，那些拥有大键或者多列的Trafodion表会以10倍于Hive表的速度增长。HBase提供了一些加密的方法，来避免对一行中的每列在磁盘上存储相同的键值。HBase同时也支持对整个数据块进行不同类型的压缩，而不管它是不是已经被加密了。有关由不同类型的加密或者压缩schema而导致的空间节省的讨论，请参考Apache HBase博客条目，“The Effect of ColumnFamily, RowKey and KeyValue Design on HFile Size，” dated FRIDAY APR 11， 2014。算法性能会作为一个相关因素，来决定压缩类型。详情请参考Govind Kamat以及Sumeet Singh陈述的第6、7、8页，“Hadoop中的压缩选项——权衡的故事 。” 有关不同类型的压缩与加密算法的出色比较，请参考附录E：HBase中的压缩与数据块加密 in the Apache HBase™ Reference Guide。

设置内存清除大小

当使用INSERT或者UPSERT USING LOAD来达到最大化数据摄取（ingestion）吞吐量的目的时，通过增加HBase表属性MEMSTORE_FLUSHSIZE能帮上忙。你使用的实际值依赖于分配给每个Region Server的大小，并发查询的工作量，以及需要同时快速数据摄取的表的数量。在一个每个Region Server有31G的空间，并且有较重的并发查询工作量的环境下，设置该属性为1G能给出较好的性能。你可以在建表的时候通过HBASE_OPTIONS子句来明确该属性的值。或者你也可以通过HBase shell来设置：alter 'TRAFODION.<schema-name>.<table-name>', MEMSTORE_FLUSHSIZE => '1073741824'

Bulk加载数据进Trafodion表

使用TrafCI或sqlci命令行界面，设置控制查询默认（Control Query Defaults，CQD）来提升加载性能：

cqd hive_max_string_length '1000'; // if the widest column is 1KB

如果在目标Trafodion表中有时间相关列的话，下面的设置就会被需要了

cqd allow_incompatible_assignment 'on'; 

从Hive中加载数据到Trafodion表，使用的LOAD语句。例：

load with no populate indexes into trafodion.sch.demo select * from hive.hive.demo;

有关LOAD语句的格式，请参考Trafodion SQL Reference Manual (pdf, 3.98 MB)。

如果多个LOAD语句一起被用来向单个大表逐渐加载数据集，那对该表的每个分区会有多个Hfile。这会导致SELECT查询时无效率的访问，同时也可能会导致数据紧缩（compaction），这得基于HBase设置中的配置策略。为了避免此种情况，针对表（短期内被两个以上的LOAD语句所加载）采取一个大的数据紧缩可以作为一个很好的实践。为了采取这样的数据紧缩，请使用HBase shell命令：

major_compact 'TRAFODION.SCH.DEMO'

这个命令不需要等待时间，并且会立即返回。通常，对一张大表采取数据紧缩会花费大量的时间（从几分钟到几小时）。你可以通过HBase Master Web界面来监控数据紧缩的进程。

Trickle加载数据进Trafodion表

odb工具能被用来向Trafodion表中添加数据。这工具是基于客户端，并通过ODBC与Trafodion相互作用。它可能被安装在Trafodion集群下，或者包含源数据的机器，再或者是用来被加载数据的中间机器。源数据可以在任何支持ODBC的RDBMS（本地或远程）上，或者odb工具所在的机器中的文件里。它允许使用该页列出的四个方法中的三个来将数据加载进Trafodion表：INSERT，UPSERT以及UPSERT USING LOAD。odb工具并不使用bulk加载的LOAD命令，并且使用odb的吞吐量可能会比使用bulk加载要低。不过，使用odb工具的话，源数据不需要独立分步移交到Trafodion集群中。odb工具的复制（copy）命令能够直接从远程RDBMS将数据复制到Trafodion表中。其中，ODBC被用来连接源与目标数据库。线程以及将数据不重复拆分，能用来实现并行。行集（rowset）用来提高吞吐量。目标Trafodion表，在复制或者使用加载命令之前，必须存在。odb工具的安装以及使用格式，在Trafodion odb用户指南(pdf, 632 KB)中有讨论。依照本页中讨论的，UPSERT USING LOAD会在odb工具支持的三种类型的insert中，给出最好的吞吐量。odb默认使用的插入方法是INSERT；想要使用UPSERT USING LOAD，请在odb加载或者复制命令中明确:loadcmd=UL。一个范例的命令行看起来像：

odb64luo -u <src_username>:<tgt_username> -p <src_pswd>:<tgt_pswd> -d <src_dsn>:<tgt_dsn> -cp src:<source_catalog>.<source_schema>.
mytable tgt:trafodion.myschema.mytable :splitby=<col-name>:parallel=4:loadcmd=UL

这里的src_username， src_pswd与src_dsn分别是连接源RDBMS所需要的用户名，密码以及ODBC数据源。同样，tgt_username，tgt_pswd与tgt_dsn就表示连接目标Trafodion实例所需的用户名，密码以及ODBC数据源。我们从源RDBMS的<source_catalog>.<source_schema>中复制表mytable到Trafodion的trafodion.myschema.mytable下。选择一个几乎平均分配的列来拆分（splitby）。如果这是一个头键列，那在一些源数据库中会表现得很好。通过使用parallel=4的选项，odb会使用4个连接来从源数据库中抓取数据，同样也使用另外4个连接来向目标Trafodion实例中写入数据。loadcmd=UL选项表明odb会使用UPSERT USING LOAD格式来写入数据。

对Trafodion表更新统计信息

为了生成良好的能加速查询的执行计划，优化器必须对于列上的值如何分布，以及不同值的数量等，有很好的了解。Trafodion通过柱状图的形式向优化器提供这样的信息，而柱状图，便是由执行UPDATE STATISTICS语句而生成的。

使用默认样品

既然精确的统计信息很重要，通过读取表中每一行来生成统计信息所需的时间可能就会非常长，但通常又没有必要。而基于表中行的随机样本，又能给出足够好的结果。对大多数情况来说，最好的选择是在UPDATE STATISTICS语句最后简单加上SAMPLE，这就会使用默认的采样协议。例如，对表T1每列，在柱状图结构上使用默认采样，你应该执行下面的语句：

update statistics for table T1 on every column sample;

这个默认的采样协议对小表而言使用一个很高的采样率，降低比率可以通过倾斜（直到1%），并将样本限制在1百万行内。默认采样的明确细节如下：

• 对不超过一万行的表，取表中的所有行。
• 对一万行到一百万行的表，随机采样一万行。实际上，这会导致采样率从100%降到1%，作为增加表大小的功能。
• 对一百万行到一亿行的表，随机采样1%。
• 对于超过一亿行的表，按照表的行数划分，采样率会按照一百万行来计算。这在确保整张表统一随机采样时，能限制总共的采样大小在一百万行以内。

从一条语句中生成单列以及多列柱状图

如果你在UPDATE STATISTICS中使用ON EVERY COLUMN格式，有一点很重要，那就是在相同语句中加入多列柱状图。例如，如果你打算对表T1的每个单独列生成柱状图，以及列集（c1，c2）、（c5，c6，c7）生成多列柱状图，你应该使用下面的语句：

update statistics for table T1 on every column, (c1,c2), (c5,c6,c7) sample;

以最后结果而言，这与下面的语句对是等价的：

update statistics for table T1 on every column sample;
update statistics for table T1 on (c1, c2), (c5, c6, c7) sample;

不过，当整合在一起时，性能更优越，那是因为多列柱状图依赖于它每列的单列柱状图。因此，将一张表中，生成单列与多列柱状图分开成两条语句，会导致对某些单列柱状图的冗余计算。即便相关的单列柱状图已经存在了，他们也会被在生成多列柱状图的时候重复计算。

启用更新统计信息自动化

如果一个标准语句集运行着，生成能提高这些语句效率的柱状图的一个方法就是，启用更新统计信息自动化，然后再PREPARE集合中的每条语句。

control query default USTAT_AUTOMATION_INTERVAL '1440';
prepare s from select...;

USTAT_AUTOMATION_INTERVAL这个CQD的值，目的是决定更新统计信息自动化的时间间隔（Interval,以分钟记）。然而，在Trafodion1.0版中，这个值并不设置为时间间隔，而是任意大于0的值。PREPARE语句会让编译器去编译以及优化一条语句，而不用真正执行它。当启用自动化后执行PREPARE时，如果优化器所需要列的柱状图（histograms）缺失的话，它会将这些列标记为“需要柱状图”。然后，下面的UPDATE STATISTICS语句会被用来在每张表上执行，以生成所需的柱状图。

update statistics for table <table-name> on necessary columns sample;

重新生成柱状图

柱状图在根本数据变动后会变的过时，并因此可能反应出值的不同分配，尽管在维护同样分配时，数据翻转或者堆积也可能会很高。为了确保统计信息能保持正确，你应该在表大变之后，从上次生成的柱状图中重新再生成一次。如果想直接刷新已经存在的柱状图，而不用添加新的，请使用下面的语句：

update statistics for table <table-name> on existing columns sample;

先前用ON NECESSARY COLUMNS格式所生成的柱状图集，可以用ON EXISTING COLUMNS语句周期性重新生成。注意，使用ON NECESSARY COLUMNS的话，只会识别那些先前被优化器所需要过，但并不存在的列。当前的自动化实施起来并不知道哪个已经存在的柱状图可能过时了。

有关UPDATE STATISTICS语句的格式，请参考Trafodion SQL Reference Manual (pdf, 3.98 MB)。

数据加载问题解答

提高吞吐量

元组列表或行集

当元组列表（Tuplelist）或者行集（Rowset）被用为数据源时，性能通常会因为元组列表或行集中的行数而提升。在某个行数下，性能会到达顶峰，并且会在之后保持相对稳定。峰值依赖于行大小，通常介于100与几千的某个值比较合理。

本地HBase表

当使用本地HBase表作为数据源时，有一点很重要，那就是忽视属性HBASE_MAX_COLUMN_VALUE_LENGTH（列智能模式，columnwise mode）以及HBASE_MAX_COLUMN_INFO_LENGTH（行智能模式，rowwise mode）的默认值，并且对正被使用为数据源的表，将它们的值设为最大。默认值可能太大了。

Hive表

当使用Hive表作为数据源时，有一点很重要，那就是对有字符串（string）格式列的Hive源表，忽视属性HIVE_MAX_STRING_LENGTH的默认值。请将该值设置为Hive表中最长字符串的长度。从Hive shell中执行下面的语句来获得这个长度：

select max(length(<col-name>)) from <hive-tab-name>;

如果该句查询出的值小于当前HIVE_MAX_STRING_LENGTH的值，你就需要提高这个值并重试。如果小很多，你可以通过减少该值来获得更好的性能。也可以使用一个近似的值。Trafodion默认的32000可能在一些情况下太大了。

监控进程

INSERT and UPSERT

对于一条INSERT语句，当交易递交时，行数据被写入能代表Trafodion表的HBase表中。这里想要看到查询进程的话，更加困难。

UPSERT USING LOAD

对于一条UPSERT USING LOAD语句，在每次调用ListOfPut成功后，添加行的过程在Trafodion表中是可见的。你可以使用一个SELECT COUNT(*)语句来监控进程。这样，你可以知道当开始执行语句时，表中已经有多少行了。

select count(*) from trafodion.sch.demo ;

LOAD

对LOAD而言，其进程会经过几个阶段，有时还会重叠。

1. Hive扫描（scan）
2. 排序（Sort）
3. 在HDFS的buld加载的临时文件夹（默认为/bulkload）中新建prep HFiles
4. 将HFile迁移到HBase

你可以在第二步的排序中，使用如下命令来监控进程：

lsof +L1 | grep SCR | wc –l

这给出了需要排序的溢出文件的数目。每个文件都是2G大小。你需要清楚正在被加载数据的大约大小，这可以让你知道还需要对多少数据进行排序。在一个集群上，可以用一个pdsh类型的工具，在所有节点上实现。Trafodion数据大小能比Hive数据大2到3倍。

在第三步中，create prep HFiles，使用这个命令来监控被写入目录的数据大小。

hadoop fs –dus /bulkload 

如果使用了压缩与加密，那大小就应该与Hive源数据的差不多。这条命令必须在一个节点上运行，并不需要在集群上重复运行。在上述目录中，可能有一些多余的数据，所以我们也应该考虑到这一点。这一步只会在排序结束之后才开始。

第四步经常耗时最短，通常不会超过几分钟。

检查计划质量

在执行一条可能要花很长时间的数据加载语句前，检查一下由SQL编译器所生成的执行计划，是很好的行为。

• 对INSERT以及UPSERT USING LOAD来说，请使用EXPLAIN语句，在Trafodion SQL Reference Manual (pdf, 3.98 MB)中描述.
• 对LOAD来说，它被作为一个工具记号（也就是说，次要SQL语句的集合）来实施，使用这条SQL语句来查看往目标表中增加数据的计划：

control query default COMP_BOOL_226 'ON' ;
prepare s1 from LOAD TRANSFORM into <target-table> <select-query-used-as-source> ;
explain options 'f' s1 ;

该计划的典型问题就是扫描不够并行。对Trafodion表，你可以使用默认的属性：PARALLEL_NUM_ESPS，来记录该问题。使用该属性的话，扫描会按照表定义的SALT分区，足够多得并行。对Hive源表，默认的属性：HIVE_NUM_ESPS_PER_DATANODE与HIVE_MIN_BYTES_PER_ESP_PARTITION，能被用来调节并行化的程度。

在采样时更新统计信息超时

更新统计信息时的采样，是使用HBase随机行筛选器（Random RowFilter）来实现的。对于几十亿行的大表而言，1百万行的样本数目会让采样率非常低。这就会导致HBase客户端连接超时错误，那是因为在一段长期的时间里，RegionServer也不会有行返回。你可以通过下面方法来避免该问题：

1. 对大表，选择超过一百万行的样本数目。这里假设T表有10亿行数据。接下来的UPDATE STATISTICS会采样一百万行的数据，也就是差不多千分之一的数据：

   update statistics for table T on every column sample;
   

   为了采样百分之一的行数，不管表的大小，你应该如下明确采样率：

   update statistics for table T on every column sample random 1 percent;
   

2. 设置hbase.rpc.timeout为比当前HBase中更高的值。

Index创建耗时太久

当建立一个索引时，Trafodion表的所有行都会被扫描到，并且列的某个子集被返回到客户端，这会花上一些时间。如果有一个与Trafodion同样数据的Hive表也被扫描的话，你可以使用默认属性：USE_HIVE_SOURCE。这会导致Hive表被用作源来建立索引。注意： Hive表必须使用Trafodion的表名作为开头。例如，如果Trafodion表是TRAFODION.SCH.DEMO，那么Hive的表明就可以是DEMO_SRC。在这情况下，设置属性如下：

control query default USE_HIVE_SOURCE '_SRC' ;
create index demo_ix on sch.demo(name)

大的Delete耗时太久或者出错

如果在一条单一的SQL语句中更新或者删除大量行，那很可能该语句就不会成功执行。在单一语句中删除或者更新超过10000行，是不被推荐的。所以大的删除或者更新，可能情况下，应该被分成多条语句执行，每条只涉及不超过10000*n行。这里的n是集群中节点的数据。

在有索引表上执行大的UPSERT USING LOAD出错

当UPSERT USING LOAD在有索引的表上使用时，该语句自动转为交易型的Upsert。这样就回到了我们之前讨论的单一语句，每条只涉及不超过10000*n（n代表节点数）行的限制上。作为一个解决方法，UPSERT USING LOAD操作能被放置在LOAD语句中，如下展示。LOAD语句会在UPSERT USING LOAD开始前禁用表中的索引。一旦UPSERT USING LOAD完成，索引又会被LOAD语句启用。

load with upsert using load into trafodion.sch.demo select * from hive.hive.demo;

Task: LOAD             Status: Started    Object: TRAFODION.SCH.DEMO
Task:  DISABLE INDEXE  Status: Started    Object: TRAFODION.SCH.DEMO
Task:  DISABLE INDEXE  Status: Ended      Object: TRAFODION.SCH.DEMO
Task:  UPSERT USING L  Status: Started    Object: TRAFODION.SCH.DEMO
       Rows Processed: 200000 
Task:  UPSERT USING L  Status: Ended      ET: 00:01:03.715
Task:  POPULATE INDEX  Status: Started    Object: TRAFODION.SCH.DEMO
Task:  POPULATE INDEX  Status: Ended      ET: 00:03:11.323