Shulou(Shulou.com)06/01 Report--

This article mainly shows you "what are the ways to call hive commands", the content is simple and clear, and I hope it can help you solve your doubts. Let me lead you to study and learn this article "how to call hive commands".

Three ways of invoking hive command

Mode 1:hive-f / root/shell/hive-script.sql (suitable for multiple statements)

Hive-script.sql is similar to script, just write query commands directly.

For example:

[root@cloud4 shell] # vi hive_script3.sql

Select * from T1

Select count (*) from T1

Do not enter interactive mode, execute a hive script

This can be used in conjunction with mute mode-S, which is called by a third-party program, and the third-party program obtains the result set through the standard output of hive.

$HIVE_HOME/bin/hive-S-f / home/my/hive-script.sql (the operation process of mapreduct will not be shown)

Mode 2:hive-e 'sql statement' (suitable for phrases)

Execute the sql statement directly for example:

[root@cloud4 shell] # hive-e 'select * from T1'

Mute mode: [root@cloud4 shell] # hive-S-e 'select * from T1' (same as the mute mode in the first mode, which does not show the operation of mapreduce)

There is also a bright spot here for exporting data to the linux local directory

For example: [root@cloud4 shell] # hive-e 'select * from T1' > test.txt

Somewhat similar to pig exporting analysis results, it is quite convenient to use 3:hive (using hive interactive mode directly)

It's all very convenient.

Introduce an interesting usage:

Syntax of 1.sql

# start hive

Hive > quit; exit hive

Hive > show databases; View the database

Hive > create database test; create database

Hive > which database does use default; use?

Hive > create table T1 (key string); create table

For creating a table, we can choose to read the file field by which character to split.

For example:

Hive > create table T1 (id)'/ wlan'

Partitioned by (log_date string) indicates partitioning through log_date

Row format delimited fields terminated by'\ t 'means to read a field by dividing it with'\ t'.

Stored as textfile/sequencefile/rcfile/; represents the format in which the file is stored

Reference address in storage format: http://blog.csdn.net/yfkiss/article/details/7787742

Textfile default format, no data compression, high disk overhead, high data parsing overhead.

It can be used in combination with Gzip and Bzip2 (the system automatically checks and decompresses the query automatically), but in this way, hive does not split the data, so it cannot operate on the data in parallel.

Example:

[plain] view plaincopy

> create table test1 (str STRING)

> STORED AS TEXTFILE

OK

Time taken: 0.786 seconds

# write a script to generate a random string file and import the file:

> LOAD DATA LOCAL INPATH'/ home/work/data/test.txt' INTO TABLE test1

Copying data from file:/home/work/data/test.txt

Copying file: file:/home/work/data/test.txt

Loading data to table default.test1

OK

Time taken: 0.243 seconds

SequenceFile is a kind of binary file support provided by Hadoop API, which is easy to use, divisible and compressible.

SequenceFile supports three compression options: NONE, RECORD, and BLOCK. Record compression ratio is low, it is generally recommended to use BLOCK compression.

Example:

[plain] view plaincopy

> create table test2 (str STRING)

> STORED AS SEQUENCEFILE

OK

Time taken: 5.526 seconds

Hive > SET hive.exec.compress.output=true

Hive > SET io.seqfile.compression.type=BLOCK

Hive > INSERT OVERWRITE TABLE test2 SELECT * FROM test1

Rcfile is a storage method that combines row and column storage. First of all, it divides the data into rows to ensure that the same record is on the same block, avoiding the need to read multiple block to read a record. Secondly, block data column storage is beneficial to data compression and fast column access. Example of a RCFILE file:

Example:

[plain] view plaincopy

> create table test3 (str STRING)

> STORED AS RCFILE

OK

Time taken: 0.184 seconds

> INSERT OVERWRITE TABLE test3 SELECT * FROM test1

Summary:

Compared with textfile and SequenceFile,rcfile due to column storage, the performance consumption of data loading is higher, but it has better compression ratio and query response. The data warehouse is characterized by one write and multiple reads, so on the whole, rcfile has obvious advantages over the other two formats.

Hive > show tables; to view all the tables in the database

Hive > show tables'* tasking queries; / / support fuzzy query

Hive > show partitions T1; / / check which partitions the table has

Hive > drop table T1; delete table

Hive does not support modifying the data in the table, but you can modify the table structure without affecting the data

The speed with local is significantly slower than without local:

Hive > load data inpath'/ root/inner_table.dat' into table T1; move data from hdfs to T1 table

Hive > load data local inpath'/ root/inner_table.dat' into table T1; upload local data to hdfs

Hive >! ls; queries the files under the current linux folder

Hive > dfs-ls /; query the files in the'/ 'directory under the current hdfs file system

The above is all the contents of the article "how to invoke hive commands". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.