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

This article mainly explains "what problems should be paid attention to when using SQL sentences in hive". The content of the explanation in this article is simple and clear, and it is easy to learn and understand. now please follow the editor's train of thought to study and learn "what problems need to be paid attention to when using SQL sentences in hive"!

Recently, there have been some problems when I am familiar with hive and use the sql statement in hive.

1 insert into statement in Phantom hive

Hive > select * from tactile hive2: OK16 2361 12 1341 2 3117 21 371 2311 12 3411 2 34Time taken: 0.218 secondshive > insert into t_hive2 values (122 secondshive 34 expecting TABLE near 2); FAILED: Parse Error: line 1:12 mismatched input 'expecting TABLE near' into' in insert clause

As can be seen from the above, hive does not support insert into statements

2, Date and DateTime types are not supported

Hive > alter table t_hive2 add columns (time_show Date); FAILED: Error in semantic analysis: DATE and DATETIME types aren't supported yet. Please use TIMESTAMP instead

3The tables of Phantom hive are divided into external tables and internal tables.

When Hive creates an internal table, it moves the data to the path pointed to by the data warehouse; if an external table is created, only the path where the data is located is recorded, and no change is made to the location of the data.

When you delete a table, the metadata and data of the internal table are deleted together, while the external table deletes only the metadata, not the data. In this way, external tables are relatively more secure, data organization is more flexible, and it is convenient to share source data.

4When the two watches are linked together, they are somewhat different from those in the past.

Hive > select * > from tactile where 2 > Parse Error: line 2:11 mismatched input', 'expecting EOF near'

5, built-in function view command (show functions;desc function function name)

Hive > desc function when;OKThere is no documentation for function 'when'Time taken: 0.095 secondshive > desc function rand;OKrand ([seed])-Returns a pseudorandom number between 0 and 1Time taken: 0.078 seconds

6 the form of truncate table table name (including delete from table name) is not supported in truncate table table name. You can use hive > dfs-rmr / user/hive/warehouse/ table name to clear the data under the table in order to keep the table metadata information from being lost, or through the create table table name like table name.

Hive > truncate table tactile failed: Parse Error: line 1:0 cannot recognize input near 'truncate'' table' 't_hive'hive > delete from table tactile hibernate usage: delete [FILE | JAR | ARCHIVE] [] *

In (subquery statement) is not supported in select id from hive, for example: in (name of the table). You can use inner join or semi-join from Table 1 left semi join Table 2 on (Table 1. Column name = Table 2. Column name), Table 2 can only appear in on and cannot be referenced in select

When writing sorting statements without global sorting, it is best to use the name of the distribute by table. Field name sort by table name. The field name is in the form of asc | desc. Try not to use order by form (only one reduce is used to complete all the sorting results)

Thank you for your reading, the above is the content of "what problems should be paid attention to when using SQL sentences in hive". After the study of this article, I believe you have a deeper understanding of what problems you need to pay attention to when using SQL sentences in hive, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.