Excel access数据整合 access表合并的命令

3Hive的数据类型和文件格式等

3.1数据单元

Databases:命名空间的功能，用于避免表，视图，分区，列的命名冲突，Databases也可以用于增强一个用户或用户组的的安全性。
Tables: 具有相同schema数据存储的位置。
Partitions:每个表可以拥有一个或多个partitions keys，这些key用于决定数据是如何存储的。
Buckets (or Clusters): 每个分区中的数据可以根据表中某列的哈希函数的值依次划分为桶。例如一个Page_view报表，可以通过其中的userid进行分桶，这个字段是所有列中一个一个，但是不是Page_view表的分区列。这种方式可以高效的抽取数据。

由于Hive的元数据可能要面临不断的更新、修改和读取、所以它显然不适合hadoop文件系统进行存储。目前Hive将元数据存储在RDBMS中，比如MySQL,Oracle,Derby中。

3.2基本数据类型

 

其中数据类型按照如下方式进行组织（一个父类是子类实例的超类型）：

3.3复合类型

复合类型，可以通过基本类型和其它负责类型构成。复合类型如下：
Structs:一个使用这种类型的元素可以通过使用DOT（.）来访问元素，例如，一个列c使用了STRUCT {a INT;b INT}类型，可以通过c.a来访问结构体数据。
Maps (key-value tuples): 访问指定域可以通过[“指定域名称”]进行，例如，一个Map M包含了一个group->gid的kv对，gid的值可以通过M[‘group’]来获取。
Arrays (indexable lists): array中的数据为相同类型，例如，假如array A中元素[‘a’,‘b’,‘c’]，则A[1]的值为’b’。
union

具体使用可以参考：

3.4JDBC数据类型

下面的表格列出了HiveServer2 JDBC中的数据类型：

3.5内置运算符和函数

官方文档中：https://cwiki.apache.org/confluence/display/Hive/Tutorial
可以通过以下命令获取最新的运算符列表、函数列表，可以通过以下方式获取使用方式：

SHOW FUNCTIONS;
DESCRIBE FUNCTION <function_name>;
DESCRIBE FUNCTION EXTENDED <function_name>;

可以通过上面的三行命令学习hive中的函数

例如：

再如：

3.5.1Hive的操作符和用户自定义函数（UDFs）

参考地址：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF

为了能够学习Hive的函数，可以构造一张dual表，构造过程如下：
1.在Hive中创建一个名字叫做dual的表，里面只有一个列dummy；
create table dual(dummy string);
2.在linux系统上，创建dual.txt文件
echo ‘Hive’ > dual.txt
3.将dual.txt文件中数据加载到hive中的dual表
直接在linux命令行下执行：

hive -e "load data local inpath '/home/hadoop/dual.txt' overwrite into table dual"

或者在hive中，直接执行

load data local inpath '/home/hadoop/dual.txt' overwrite into table dual;

4.执行完上面命令之后，回到hive中，尝试下

select 'test' from dual;

结果如下：

OK
test
Time taken: 0.055 seconds, Fetched: 1 row(s)

3.6HIVE命令和CLI

3.6.1Hive命令设置

Hive的命令就是一个非SQL语句，例如设置一个属性或者添加一个资源，他们可以使用在HiveQL脚本或者直接在CLI或者Beeline中使用。
例如：

hive> set mapred.reduce.tasks=32;
hive> set;
hive> select a.* from tab1;
hive> !ls;
hive> dfs -ls;

更多可以参考（https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Commands）：

3.6.2Hive Resources

Hive可以管理向会话添加的资源，这些资源需要在查询执行时可用，这些资源可能是files,jars,or archives.只有本地的可访问的文件可以添加到会话中。
一旦将资源添加到会话中，Hive查询就可以通过它的名称(在map/reduce/transform子句中)引用它，并且在整个Hadoop集群的执行时，资源在本地可用。Hive使用Hadoop的分布式缓存在查询执行时将添加的资源分发到集群中的所有机器。
具体语法是：

ADD { FILE[S] | JAR[S] | ARCHIVE[S] } <filepath1> [<filepath2>]*

LIST { FILE[S] | JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]

DELETE { FILE[S] | JAR[S] | ARCHIVE[S] } [<filepath1> <filepath2> ..]

FILE：文件资源只是添加到分布式缓存中。
JAR：文件资源被添加到类路径Java classpath中。这需要引用对象，例如包含UDFs
ARCHIVE：存档资源作为分发资源的一部分自动不存档

例子：

hive> add FILE /tmp/tt.py;
  hive> list FILES;
  /tmp/tt.py
  hive> select from networks a 
               MAP a.networkid 
               USING 'python tt.py' as nn where a.ds = '2009-01-04' limit 10;

再如：
添加临时函数，只能在此会话中生效，退出hive自动失效：

hive> add jar /home/jtdata/hiveUDF/out0.jar;
hive> create temporary function quling as 'com.redhadoop.dataout0.DayOut0';

添加永久函数：

hadoop-1 ~]# cd $HIVE_HOME 
hadoop-1 hive-client]# mkdir auxlib
hadoop-1 auxlib]# ls
Month.jar  out0.jar
hive> create function quling as 'com.redhadoop.dataout0.DayOut0';

添加永久函数：

hadoop-1 conf]# vim hive-site.xml
<property>
    <name>hive.aux.jars.path</name>
    <value>file:///home/hiveUDF/Month.jar,file:///home/hiveUDF/OutZ.jar</value>
</property>

3.6.3Hive的命令行操作

获取帮助，可以运行”hive –H”或”hive –help”,例如：

即：

usage: hive
 -d,--define <key=value>          Variable substitution to apply to Hive
                                  commands. e.g. -d A=B or --define A=B
    --database <databasename>     Specify the database to use
 -e <quoted-query-string>         SQL from command line
 -f <filename>                    SQL from files
 -H,--help                        Print help information
    --hiveconf <property=value>   Use value for given property
    --hivevar <key=value>         Variable substitution to apply to Hive
                                  commands. e.g. --hivevar A=B
 -i <filename>                    Initialization SQL file
 -S,--silent                      Silent mode in interactive shell
 -v,--verbose                     Verbose mode (echo executed SQL to the
                                  console)

例：
通过在命令行中与运行：

$HIVE_HOME/bin/hive -e 'select a.col from tab1 a'

设置Hive配置变量的例子：

$HIVE_HOME/bin/hive -e 'select a.col from tab1 a' --hiveconf hive.exec.scratchdir=/home/my/hive_scratch  --hiveconf mapred.reduce.tasks=32

静默模式下，通过一个查询语句的方式dump数据到一个文件中：

$HIVE_HOME/bin/hive -S -e 'select a.col from tab1 a' > a.txt

运行本地sql脚本案例：

$HIVE_HOME/bin/hive -f /home/my/hive-script.sql

运行hadoop文件系统中的一个SQL脚本：

$HIVE_HOME/bin/hive -f hdfs://<namenode>:<port>/hive-script.sql

3.6.4Hive批处理模式

当$HIVE/bin/hive通过使用-e 或者 –f命令的时候，它执行SQL的模式是通过批处理模式进行的。

hive -e '<query-string>' 执行查询字符串.
hive -f <filepath> 在文件中执行一个或多个SQL查询.

3.7Hive文件格式

3.7.1Avro Files

从Hive 0.14版本之后，基于Avro的表可以通过在DDL语句中加上” STORED AS AVRO”的方式很容的被创建
例如：

CREATE TABLE test_serializer(string1 STRING,
                             int1 INT,
                             tinyint1 TINYINT,
                             smallint1 SMALLINT,
                             bigint1 BIGINT,
                             boolean1 BOOLEAN,
                             float1 FLOAT,
                             double1 DOUBLE,
                             list1 ARRAY<STRING>,
                             map1 MAP<STRING,INT>,
                             struct1 STRUCT<sint:INT,sboolean:BOOLEAN,sstring:STRING>,
                             union1 uniontype<FLOAT, BOOLEAN, STRING>,
                             enum1 STRING,
                             nullableint INT,
                             bytes1 BINARY,
                             fixed1 BINARY)
 ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY ':' MAP KEYS TERMINATED BY '#' LINES TERMINATED BY '\n'
 STORED AS TEXTFILE;

上面的数据格式如下：

why hello there,42,3,100,1412341,true,42.43,85.23423424,alpha:beta:gamma,Earth#42:Control#86:Bob#31,17:true:Abe Linkedin,0:3.141459,BLUE,72,^A^B^C,^A^B^C
another record,98,4,101,9999999,false,99.89,0.00000009,beta,Earth#101,1134:false:wazzup,1:true,RED,NULL,^D^E^F^G,^D^E^F
third record,45,5,102,999999999,true,89.99,0.00000000000009,alpha:gamma,Earth#237:Bob#723,102:false:BNL,2:Time to go home,GREEN,NULL,^H,^G^H^I

格式如：

将数据LOAD DATA到表中

hive> LOAD DATA LOCAL INPATH '/root/test/test_serializer' OVERWRITE INTO TABLE test_serializer;

查询方式：

hive> select list1[0],map1['Earth'],struct1.sint,union1 from test_serializer;
OK
alpha	42	17	{0:3.141459}
beta	101	1134	{1:true}
alpha	237	102	{2:"Time to go home"}
Time taken: 0.181 seconds, Fetched: 3 row(s)
hive>

你可以通过以下方式将它写入到一个Avro:

CREATE TABLE as_avro(string1 STRING,
                     int1 INT,
                     tinyint1 TINYINT,
                     smallint1 SMALLINT,
                     bigint1 BIGINT,
                     boolean1 BOOLEAN,
                     float1 FLOAT,
                     double1 DOUBLE,
                     list1 ARRAY<STRING>,
                     map1 MAP<STRING,INT>,
                     struct1 STRUCT<sint:INT,sboolean:BOOLEAN,sstring:STRING>,
                     union1 uniontype<FLOAT, BOOLEAN, STRING>,
                     enum1 STRING,
                     nullableint INT,
                     bytes1 BINARY,
                     fixed1 BINARY)
STORED AS AVRO;
INSERT OVERWRITE TABLE as_avro SELECT * FROM test_serializer;

3.7.2ORC Files

具体可以参考：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+ORC
Orc文件格式是一种最优的Row Columnar(ORC)文件格式，它提供了一种高效的方式去存储Hive的数据。它的设计目的是克服其它数据格式的限制。通过使用Orc文件，可以提升Hive在读取，写入和处理数据的性能
使用ORC文件具有以下几种优势：
将单个文件作为每个任务的输出，从而减少NameNode的负载。
支持Hive的数据类型包括 datetime, decimal, 和复合类型 (struct, list, map,及union)
文件中存储的轻量级索引

skip row groups that don't pass predicate filtering
seek to a given row

使用单独的RecordReader并发读取多个文件
能够分割文件，而不需要扫描标记（ability to split files without scanning for markers）
限制读写所需的内存（bound the amount of memory needed for reading or writing）
使用协议缓冲区存储的元数据，允许添加和删除字段（metadata stored using Protocol Buffers, which allows addition and removal of fields）

HiveQL语法

CREATE TABLE ... STORED AS ORC

ALTER TABLE ... [PARTITION partition_spec] SET FILEFORMAT ORC

SET hive.default.fileformat=Orc

所有关于ORC文件的参数都被放在TBLPROPERTIES中，它们是：

例如创建一个不使用压缩算法的ORC表存储：

hive> create table Addresses(
    > name string,
    > street string,
    > city string,
    > state string,
    > zip int
    > ) stored as orc tblproperties("orc.compress"="NONE");
OK
Time taken: 0.203 seconds
hive>

在Hive 0.14版本之后出现：CONCATENATE，通过使用ALTER TABLE table_name [PARTITION partition_spec] CONCATENATE 可以合并小的ORC文件到一个大的文件中。

ORC File Dump Utility

The ORC file dump utility analyzes ORC files.  To invoke it, use this command:

// Hive version 1.3.0 and later:
hive --orcfiledump [-j] [-p] [-d] [-t] [--rowindex <col_ids>] [--recover] [--skip-dump] 
    [--backup-path <new-path>] <location-of-orc-file-or-directory>

3.7.3Parquet

Parquet支持以下引擎：
Apache Hive
Apache Drill
Cloudera Impala
Apache Crunch
Apache Pig
Cascading
Apache Spark

HiveQL语法
针对Hive 0.13及其以后版本

hive> CREATE TABLE parquet_test (
    >  id int,
    >  str string,
    >  mp MAP<STRING,STRING>,
    >  lst ARRAY<STRING>,
    >  strct STRUCT<A:STRING,B:STRING>) 
    > PARTITIONED BY (part string)
    > STORED AS PARQUET;
OK
Time taken: 0.202 seconds
hive>

3.7.4数据压缩

在某些情况下，将数据压缩到Hive表中会比未压缩的存储提供更好的性能;无论是在磁盘使用情况还是查询性能方面
您可以直接将用Gzip或Bzip2压缩的文本文件导入到作为TextFile存储的表中。压缩将自动检测到，并在查询执行期间实时解压缩文件。例如:

hive> CREATE TABLE raw (line STRING)
    >    ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
OK
Time taken: 0.192 seconds
hive> LOAD DATA LOCAL INPATH '/root/test/test_serializer.gz' INTO TABLE raw;
Loading data to table test_database.raw
OK
Time taken: 0.579 seconds
hive> select * from raw;
OK
test_serializer0000644000000000000000000000064113472676126012726 0ustar  rootrootwhy hello there,42,3,100,1412341,true,42.43,85.23423424,alpha:beta:gamma,Earth#42:Control#86:Bob#31,17:true:Abe Linkedin,0:3.141459,BLUE,72,^A^B^C,^A^B^C
another record,98,4,101,9999999,false,99.89,0.00000009,beta,Earth#101,1134:false:wazzup,1:true,RED,NULL,^D^E^F^G,^D^E^F
third record,45,5,102,999999999,true,89.99,0.00000000000009,alpha:gamma,Earth#237:Bob#723,102:false:BNL,2:Time to go home,GREEN,NULL,^H,^G^H^I

Time taken: 0.63 seconds, Fetched: 4 row(s)
hive> select * from raw;

这个表’raw’以TextFile的方式存储，这个TextFile也是默认存储。但是，在一些情况下，Hadoop不能将文件分割成chunks/blocks,并且并行的运行在多个maps中。这可能会导致你集群中的map功能未被充分利用。

推荐的做法是以SequenceFile存储格式的方式将数据插入到一个表。一个SequenceFile可以被Hadoop分割并分布在map作业中，而GZIP文件则不能。例如:

CREATE TABLE raw (line STRING)
   ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
 
CREATE TABLE raw_sequence (line STRING)
   STORED AS SEQUENCEFILE;
 
LOAD DATA LOCAL INPATH '/tmp/weblogs/20090603-access.log.gz' INTO TABLE raw;
 
SET hive.exec.compress.output=true;
SET io.seqfile.compression.type=BLOCK; -- NONE/RECORD/BLOCK (see below)
INSERT OVERWRITE TABLE raw_sequence SELECT * FROM raw;

3.7.5LZO 压缩

LZO是一个无损的数据压缩库，它测重在速度，而不是压缩比。假设一个简单的文件中含有3列,列名是：

id,
first name
last name

模拟一下数据：

19630001     john          lennon
19630002     paul          mccartney
19630003     george        harrison
19630004     ringo         starr

把它放到”/path/to/dir/names.txt”中。

先决条件
Lzo/Lzop需要在hadoop cluster的每个节点中都安装，需要在hadoop的core-site.xml中添加以下内容：

com.hadoop.compression.lzo.LzoCodec
com.hadoop.compression.lzo.LzopCodec

例如：

<property>
    <name>io.compression.codecs</name>
    <value>org.apache.hadoop.io.compress.GzipCodec,org.apache.hadoop.io.compress.DefaultCodec,org.apache.hadoop.io.compress.BZip2Codec,com.hadoop.compression.lzo.LzoCodec,com.hadoop.compression.lzo.LzopCodec</value>
</property>

<property>
    <name>io.compression.codec.lzo.class</name>
    <value>com.hadoop.compression.lzo.LzoCodec</value>
</property>

使用以下方式创建一个LZO index file:

hadoop jar /path/to/jar/hadoop-lzo-cdh4-0.4.15-gplextras.jar com.hadoop.compression.lzo.LzoIndexer  /path/to/HDFS/dir/containing/lzo/files

这个命令在HDFS上创建names.txt.lzo

Table Definition
下面通过”hive -e”命令创建一个LZO压缩格式的外部表

hive -e "CREATE EXTERNAL TABLE IF NOT EXISTS hive_table_name (column_1  datatype_1......column_N datatype_N)
         PARTITIONED BY (partition_col_1 datatype_1 ....col_P  datatype_P)
         ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
         STORED AS INPUTFORMAT  \"com.hadoop.mapred.DeprecatedLzoTextInputFormat\"
                   OUTPUTFORMAT \"org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat\";

注意：在hive –e中的双引号必须转义。

Hive Queries
Option 1:直接创建LZO文件
1、直接创建LZO文件作为Hive查询的输出。
2、使用lzop命令实用程序或自定义Java生成.lzo。.lzo文件的索引。
Hive Query Parameters

SET mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzoCodec

SET hive.exec.compress.output=true

SET mapreduce.output.fileoutputformat.compress=true

例如：

hive -e "SET mapreduce.output.fileoutputformat.compress.codec=com.hadoop.compression.lzo.LzoCodec; SET hive.exec.compress.output=true;SET mapreduce.output.fileoutputformat.compress=true; <query-string>"

Note: If the data sets are large or number of output files are large , then this option does not work.

Option 2: 编写自定义Java来创建LZO文件
1、创建一个text files作为hive查询的输出。
2、编写自定义Java代码：
A、将Hive查询生成的文本文件转换为.lzo文件
B、生成.lzo。上面生成的.lzo文件的索引文件

Hive Query参数：

SET hive.exec.compress.output=false
SET mapreduce.output.fileoutputformat.compress=false

例如：

hive -e "SET hive.exec.compress.output=false;SET mapreduce.output.fileoutputformat.compress=false;<query-string>"