Hive (vii): HQL DML

HQL DML mainly involves data manipulation in the Hive table, including: Load, INSERT, DELETE, EXPORT and IMPORT, detailed information see:https://cwiki.apache.org/ Confluence/display/hive/languagemanual+dml

Directory:

• Loading files into tables
• Inserting data into Hive Tables from queries
• Writing data into the filesystem from queries
• Inserting values into tables from SQL
• Delete
• Application Demo

Loading files into tables:

• Syntax: load DATA [LOCAL] inpath ' filepath ' [OVERWRITE] into TABLE tablename [Partiti On (Partcol1=val1, Partcol2=val2 ...)]   

•  # Creating Tables CREATE Table Web_log (Viewtime INT, userid BIGINT, url string, referrer string, IP string) ROW FORMAT delimited fields T erminated by   " \t   '  

1. The contents (if any) in the target table (or partition) are deleted, and then the contents of the file/directory pointed to by FilePath are added to the table/partition
2. If the target table (partition) already has a file, and the file name and file name in the filepath conflict, then the existing files will be replaced by the new file

Inserting data into Hive Tables from queries:

• syntax: INSERT overwrite TABLE tablename1 [PARTITION] select_statement1 from From_statement;
           INSERT into TABLE tablename1 [PARTITION] select_statement1 from from_statement;

•   #创建结构相同的表create table EmpDemo  as   employee; #插入数据insert into table EmpDemo  select  * from   employee; #覆盖插入数据insert Overwrite table EmpDemo  select  * from  employee; 

  view code

Writing data into the filesystem from queries:

• Syntax: INSERT OVERWRITE [LOCAL] DIRECTORY directory1 SELECT ... From ...
• Example code:

  ' ./tmp/zhu '  SELECT * from employee;

  View Code

Inserting values into tables from SQL:

• Syntax: INSERT into TABLE tablename VALUES values_row [, Values_row ...]

Example code:

#单行插入insert into Table employee values ('001','001','Tgzhu'); #多行插入insert into table employee values ('004','004','Wangwu'),('005','005','Zhaozhao');

View Code

Delete:

• Syntax: DELETE from TableName [WHERE expression]

Application Demo:

• To illustrate the DDL and DML of hive with a practical application, the process is as follows
• Create the external table associated with HBase, hql as follows:

  CREATEEXTERNALTABLE if  not existsHive_cm_evcrealtimedata (Rowkey string, realtimedata_carno string, Realtimedata_time str ING, realtimedata_speeddecimal( -,8), Realtimedata_mileagedecimal( -,8), Realtimedata_highestvoltagebatteryordint, Realtimedata_latitudedecimal( -,8), Realtimedata_longitudedecimal( -,8)) STORED by 'Org.apache.hadoop.hive.hbase.HBaseStorageHandler'   withSerdeproperties ('hbase.columns.mapping' = ': Key,d:realtimedata_carno, Ata_time,d:realtimedata_speed,d:realtimedata_mileage,d:realtimedata_ Highestvoltagebatteryord,d:realtimedata_latitude,d:realtimedata_longitude') tblproperties ('Hbase.table.name' = 'Cm_evcrealtimedata')

  View Code
• Create a hive real table to hold the results of the calculation, as follows:

CREATE TABLE if  not existshive_cm_cardailyrpt (Carno string, DTime string, Onlinecountint, Runcountint, Mileagedecimal( -,8), Maxspeeddecimal( -,8), Totalpowerdecimal( -,8), Averagespeeddecimal( -,8), Cdi_batteryflag string, cdi_coordinatorflag string) STORED asTextfile

View Code

• Calculate and insert the result into a real table, as follows:

SetHive.execution.engine=Tez; InsertOverwriteTablehive_cm_cardailyrptSelectCarno,dtime,CONVERT(int,SUM(CT)) asOnlinecount,CONVERT(int,SUM(Ctspeed)) asRuncount,CONVERT(decimal( -,2),MAX(MILE)-MIN(MILE)) asMileage,CONVERT(decimal( -,2),MAX(speed)) asMaxspeed, ((MAX(MILE)-MIN(MILE))*0.2) asTotalpower, Case  when SUM(Ctspeed)>0  Then CONVERT(decimal( -,2),((MAX(MILE)-MIN(MILE))/SUM(ctspeed))) Else 0 End  asAveragespeed, Case  when SUM(Realtimedata_highestvoltagebatteryord)>0  Then '0' Else '1' End  asBatteryflag, Case  when(SUM(Realtimedata_latitude)+ SUM(Realtimedata_longitude))>0  Then '0' Else '1' End  asLatitudeflag, from     (     SELECTRealtimedata_carno asCarno,substring(Realtimedata_time,1,8) asDTime,1  asCT, Case  whenRealtimedata_speed>0  Then 1 ELSE 0 END  asCtspeed, Case  whenRealtimedata_mileage=0  Then NULL ELSERealtimedata_mileageEND  asMILE, Case  whenRealtimedata_speed> $  Then 0 ElseRealtimedata_speedEnd  asSpeed , Realtimedata_highestvoltagebatteryord, Realtimedata_latitude,realtimedata_longitude  fromcm_evcrealtimedata) TGroup  byCarno,dtime

View Code

Hive (vii): HQL DML