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

This article introduces the relevant knowledge of "what are the common sql of hive?". In the operation of actual cases, many people will encounter such a dilemma. Then let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.hive group deduplicates the function to use.

Select *, row_num () over (partition by id order by modifytime desc) rn from lyjtest where rn=1

The row_num () over function partitions the id in descending order according to the modification time, and then filters out the data of the latest time (rn=1) to achieve the effect of de-duplication.

2.hive writes data

Insert into table table2 select * from table1;-query the data in table1 to write to table2

Insert overwrite table table2 select * from table1;-- overwrite

The difference between 3.where and having

/ / where is limited before grouping (for raw data filtering, where cannot filter aggregate functions)

Hive > select count (*), age from table1 where id > 18 group by age

/ / having is grouped first under the qualifying condition (each group is filtered and having can only be followed by the existing columns in select)

Hive > select age,count (*) c from table1 group by age having c > 2

/ / where is used with having

Select id,count (*) from table1 where id > 18 group by id having count (*) > 2

4.hive only supports union all, not union

Union all does not remove weight.

Select name,age from table1 where id18

5. Query the first five pieces of data

Select * from table1 order by age desc limit 5;-- query the five oldest pieces of data

Select * from student limit 5 words-randomly query five pieces of data

6. The strict order of five clauses

Where → group by → having → order by → limit

7.distinct

The / / distinct keyword returns a unique different value (returns records with different age and id)

Hive > select distinct age,id from test

8. Copy tabl

Create table test1_temp like test1;-only replicated tables do not contain data

Create table test1 as select * from test2;-- copy the data from the replication table to the new table 9. Create a tabl

9. Create a tabl

CREATE TABLE `lyjtest1` (`id` double, `name` string, `sex` string) COMMENT 'create table from sql'ROW FORMAT SERDE' org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe' WITH SERDEPROPERTIES ('field.delim'='\ tress,' line.delim'='\ n' 'serialization.format'='\ t') STORED AS INPUTFORMAT' org.apache.hadoop.mapred.TextInputFormat'OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'LOCATION' hdfs://ambari1:8020/warehouse/tablespace/managed/hive/ods_lyjtest.db/lyjtest1' This is the end of the content of "what are the sql commonly used in hive"? thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.