Hive Simple SQL

1. Create a table

(1) Differences between internal and external tables

The default is to create the internal table, you can specify the directory, if not specified will create a default directory, once drop, the directory and data will be deleted

When you create a external table, you need to specify a directory, and when the drop table is dropped, the data in that directory and directory will not be deleted, only the meta-information will be deleted.

#创建一个外部表

0:jdbc:hive2://192.168.163.102:10000> Create external Table T10 (c1 int,c2 string) row format delimited terminated by ', ' stored as Testfile Location "/dir1";

[Email protected] tmp]# HDFs dfs-put file1/dir1

[Email protected] tmp]# HDFs dfs-ls-r/dir1

-rw-r--r--1 root supergroup 2017-11-25 20:53/dir1/file1

0:jdbc:hive2://192.168.163.102:10000> drop table T10;

No rows affected (0.41 seconds)

[Email protected] tmp]# HDFs dfs-ls-r/dir1

17/11/25 20:56:41 WARN util. nativecodeloader:unable to load Native-hadoop library for your platform ... using Builtin-java classes where applicable

-rw-r--r--1 root supergroup 2017-11-25 20:53/dir1/file1

#创建一个默认的内部表

0:jdbc:hive2://192.168.163.102:10000> CREATE TABLE T2 (c1 int,c2 string) row format delimited fields terminated by ', ' stored as textfile;

(2) storage file format supported by hive

Textfile, Sequencefile, Orc, Parquet,avro

0:jdbc:hive2://192.168.163.102:10000> CREATE TABLE T5 (c1 int,c2 string) row format delimited fields terminated by ', ' stored as sequencefile;

0:jdbc:hive2://192.168.163.102:10000> INSERT INTO T5 select * from T4;

#作为sequencefile格式存储的文件无法直接查看其内容

[Email protected] tmp]# HDFs dfs-ls/user/hive/warehouse/testdb1.db/t5/

-rwxr-xr-x 1 root supergroup 146 2017-11-26 03:03/user/hive/warehouse/testdb1.db/t5/000000_0

0:jdbc:hive2://192.168.163.102:10000> DESC formatted T5;

2. Import data to Hive

Grammar:

LOAD DATA [LOCAL] inpath ' filepath ' [OVERWRITE] into TABLE tablename [PARTITION (Partcol1=val1, Partcol2=val2 ...)]

(1) Direct import of local files to a table in hive

0:jdbc:hive2://192.168.163.102:10000> Load Data local inpath '/tmp/file1 ' into table T1;

0:jdbc:hive2://192.168.163.102:10000> select * from T1;

+--------+--------+--+

| T1.c1 | T1.C2 |

+--------+--------+--+

| 1 | AAA |

| 2 | BBB |

| 3 | CCC |

| 4 | DDD |

+--------+--------+--+

(2) loading the data into the table, but overwriting all the data in the table, essentially overwriting all the files in the T1 directory

0:jdbc:hive2://192.168.163.102:10000> Load Data local inpath '/tmp/file3 ' overwrite into table T1;

No rows affected (0.597 seconds)

0:jdbc:hive2://192.168.163.102:10000> select * from T1;

+--------+---------+--+

|  T1.c1 | T1.C2 |

+--------+---------+--+

| 1 | Yiyi |

| 2 | Erer |

| 3 | SanSan |

| 4 | Sisi |

+--------+---------+--+

4 rows selected (0.073 seconds)

(3) Importing a file from HDFs to a table in hive

[Email protected] tmp]# Cat/tmp/file2

5,eee

[Email protected] tmp]# HDFs dfs-put/tmp/file2/user/hive/warehouse/testdb1.db/t1

0:jdbc:hive2://192.168.163.102:10000> load Data inpath '/user/hive/warehouse/testdb1.db/t1/file2 ' into table T1;

0:jdbc:hive2://192.168.163.102:10000> select * from T1;

+--------+--------+--+

| T1.c1 | T1.C2 |

+--------+--------+--+

| 1 | AAA |

| 2 | BBB |

| 3 | CCC |

| 4 | DDD |

| 5 | eee |

+--------+--------+--+

(4) Create another table based on one table and insert data at the same time

0:jdbc:hive2://192.168.163.102:10000> CREATE TABLE t2 as select * from T1;

(5) Create a table structure based on a table, then insert the data

0:jdbc:hive2://192.168.163.102:10000> CREATE table t3 like T1;

0:jdbc:hive2://192.168.163.102:10000> INSERT INTO T3 select * from T1;

3, from the query results to the file system to guide the data

(1) from the query result data to the HDFs file system

0:jdbc:hive2://192.168.163.102:10000> select * from T1;

+--------+---------+--+

|  T1.c1 | T1.C2 |

+--------+---------+--+

| 1 | Yiyi |

| 2 | Erer |

| 3 | SanSan |

| 4 | Sisi |

+--------+---------+--+

0:jdbc:hive2://192.168.163.102:10000> Insert Overwrite directory '/user/hive/warehouse/tmp ' select * FROM TESTDB1.T1;

[Email protected] tmp]# HDFs dfs-ls-r/user/hive/warehouse/tmp

-rwxr-xr-x 1 root supergroup 2017-11-26 00:25/user/hive/warehouse/tmp/000000_0

[Email protected] tmp]# HDFs dfs-get/user/hive/warehouse/tmp/000000_0/tmp/

The ASCII code for the exported file delimiter is CTRL + a \001

[Email protected] tmp]# Vim/tmp/000000_0

1^ayiyi

2^aerer

3^asansan

4^asisi

Use this file to create an external table, using \001 as a delimiter

0:jdbc:hive2://192.168.163.102:10000> Create external Table T5 (c1 int,c2 string) row format delimited fields terminate D by ' \001 ' location '/user/hive/warehouse/tmp/';

0:jdbc:hive2://192.168.163.102:10000> SELECT * from T5;

+--------+---------+--+

|  T5.c1 | T5.C2 |

+--------+---------+--+

| 1 | Yiyi |

| 2 | Erer |

| 3 | SanSan |

| 4 | Sisi |

+--------+---------+--+

(2) Guide data from query results to local

0:jdbc:hive2://192.168.163.102:10000> Insert overwrite local directory '/tmp ' SELECT * from Testdb1.t1;

[Email protected] tmp]# Ls/tmp/000000_0

/tmp/000000_0

4 Insert

(1) Insert inserts the essence of the data is to create a file

0:jdbc:hive2://192.168.163.102:10000> INSERT INTO T5 values (4, ' Sisi ');

No rows affected (17.987 seconds)

0:jdbc:hive2://192.168.163.102:10000> dfs-ls/user/hive/warehouse/testdb1.db/t5  ;

+-------------------------------------------------------------------------------------------------------------- --+--+

| DFS Output |

+-------------------------------------------------------------------------------------------------------------- --+--+

| Found 2 Items |

| -rwxr-xr-x 1 root supergroup 146 2017-11-26 03:03/user/hive/warehouse/testdb1.db/t5/000000_0 |

| -rwxr-xr-x 1 root supergroup 106 2017-11-26 04:22/user/hive/warehouse/testdb1.db/t5/000000_0_copy_1 |

+-------------------------------------------------------------------------------------------------------------- --+--+

Hive Simple SQL