类似Mysql的数据库概念:
hive> CREATE DATABASE cui;
hive> USE cui;
创建表:
CREATE TABLE test(
first STRING,
second STRING
)
默认记录和字段分隔符:
\n 每行一条记录
^A 分隔列(八进制 \001)
^B 分隔ARRAY或者STRUCT中的元素,或者MAP中多个键值对之间分隔(八进制 \002)
^C 分隔MAP中键值对的“键”和“值”(八进制 \003)
自定义分隔符:
CREATE TABLE test(
……
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\001'
COLLECTION ITEMS TERMINATED BY '\002'
MAP KEYS TERMINATED BY '\003'
LINES TERMINATED BY '\n'
查看信息:
DESCRIBE DATABASE cui;
DESCRIBE DATABASE EXTENDED cui;
分区表:
CREATE TABLE test(
……
)
PARTITIONED BY ( country STRING ); #分区键和字段不能重复
------------------------------------------------------------------
加载数据:
LOAD DATA LOCAL INPATH '/path/to/local/files'
OVERWRITE INTO TABLE test
PARTITION (country='CHINA')
有LOCAL表示从本地文件系统加载(文件会被拷贝到HDFS中)
无LOCAL表示从HDFS中加载数据(注意:文件直接被移动!!!而不是拷贝!!! 并且。。文件名都不带改的。。)
OVERWRITE 表示是否覆盖表中数据(或指定分区的数据)(没有OVERWRITE 会直接APPEND,而不会滤重!)
关于加载数据的LOCAL关键字:
【使用前】:
[cui@node1 ~]$ hadoop fs -ls /workspace
Found 2 items
-rw-r--r-- 1 yjt-app1-web1 supergroup 1716110 2016-01-14 16:31 /workspace/IOS_OPERA_STATISTICS_20160113.01.DAT
-rw-r--r-- 1 yjt-app1-web1 supergroup 520964539 2016-01-14 21:20 /workspace/bpUserinfo_201511.log
【使用中】:
hive> LOAD DATA INPATH '/workspace/bpUserinfo_201511.log' INTO table testkv;
Loading data to table default.testkv
Table default.testkv stats: [numFiles=3, numRows=0, totalSize=520964575, rawDataSize=0]
OK
Time taken: 1.015 seconds
【使用后】:
[cui@node1 ~]$ hadoop fs -ls /workspace
Found 1 items
-rw-r--r-- 1 yjt-app1-web1 supergroup 1716110 2016-01-14 16:31 /workspace/IOS_OPERA_STATISTICS_20160113.01.DAT
[cui@node1 ~]$ hadoop fs -ls /user/hive/warehouse/testkv
Found 5 items
drwxrwxr-x - yjt-app1-web1 supergroup 0 2016-01-14 21:06 /user/hive/warehouse/testkv/.hive-staging_hive_2016-01-14_21-06-53_158_2726638877292950395-1
drwxrwxr-x - yjt-app1-web1 supergroup 0 2016-01-14 21:08 /user/hive/warehouse/testkv/.hive-staging_hive_2016-01-14_21-08-59_461_1557138301562621871-1
-rwxrwxr-x 1 yjt-app1-web1 supergroup 520964539 2016-01-14 21:20 /user/hive/warehouse/testkv/bpUserinfo_201511.log
-rwxrwxr-x 1 yjt-app1-web1 supergroup 24 2016-01-14 21:09 /user/hive/warehouse/testkv/part-00000
-rwxrwxr-x 1 yjt-app1-web1 supergroup 12 2016-01-14 21:09 /user/hive/warehouse/testkv/part-00001
如果加载同样文件名的文件,会被自动重命名:
【使用前】:
[cui@node1 ~]$ cp ~/oltpUserSqlLogs/crm/data/bpUserinfo_201504.log .
[cui@node1 ~]$ mv bpUserinfo_201504.log bpUserinfo_201511.log
[cui@node1 ~]$ hadoop fs -put bpUserinfo_201511.log /workspace
[cui@node1 ~]$ hadoop fs -ls /workspace
Found 2 items
-rw-r--r-- 1 yjt-app1-web1 supergroup 1716110 2016-01-14 16:31 /workspace/IOS_OPERA_STATISTICS_20160113.01.DAT
-rw-r--r-- 1 yjt-app1-web1 supergroup 40393299 2016-01-15 13:20 /workspace/bpUserinfo_201511.log
【使用中】:
hive> LOAD DATA INPATH '/workspace/bpUserinfo_201511.log' INTO table testkv;
Loading data to table default.testkv
Table default.testkv stats: [numFiles=4, numRows=0, totalSize=561357874, rawDataSize=0]
OK
Time taken: 1.745 seconds
【使用后】:
[cui@node1 ~]$ hadoop fs -ls /workspace
Found 1 items
-rw-r--r-- 1 yjt-app1-web1 supergroup 1716110 2016-01-14 16:31 /workspace/IOS_OPERA_STATISTICS_20160113.01.DAT
[cui@node1 ~]$ hadoop fs -ls /user/hive/warehouse/testkv
Found 6 items
drwxrwxr-x - yjt-app1-web1 supergroup 0 2016-01-14 21:06 /user/hive/warehouse/testkv/.hive-staging_hive_2016-01-14_21-06-53_158_2726638877292950395-1
drwxrwxr-x - yjt-app1-web1 supergroup 0 2016-01-14 21:08 /user/hive/warehouse/testkv/.hive-staging_hive_2016-01-14_21-08-59_461_1557138301562621871-1
-rwxrwxr-x 1 yjt-app1-web1 supergroup 520964539 2016-01-14 21:20 /user/hive/warehouse/testkv/bpUserinfo_201511.log
-rwxrwxr-x 1 yjt-app1-web1 supergroup 40393299 2016-01-15 13:20 /user/hive/warehouse/testkv/bpUserinfo_201511_copy_1.log
-rwxrwxr-x 1 yjt-app1-web1 supergroup 24 2016-01-14 21:09 /user/hive/warehouse/testkv/part-00000
-rwxrwxr-x 1 yjt-app1-web1 supergroup 12 2016-01-14 21:09 /user/hive/warehouse/testkv/part-00001