2023学年完整公开课版HiveshellDML

HiveshellDML目

录数据导入

1数据导出2数据插入3数据导入-LOAD基本语法LOADDATA[LOCAL]INPATH'filepath'[OVERWRITE]INTOTABLEtablename[PARTITION(partcol1=val1,partcol2=val2...)]LOCAL：本地文件INPATH：文件路名（文件名或目录）OVERWRITE：覆盖导入PARTITION：导入分区从本地数据文件加载到Hive表中将本地文件复制到Hive对应的HDFS目录中。数据导入-LOAD从HDFS加载数据文件到Hive表中将HDFS源数据文件移动到Hive表对应的HDFS目录中（外部表除外）数据导入-LOAD单表插入INSERTOVERWRITE|INTOTABLEtablename1[PARTITION(partcol1=val1,partcol2=val2...)]select_statement1FROMfrom_statement;OVERWRITE：采用覆盖方式插入INTO：采用追加方式插入数据导入-INSERT数据导入-INSERT多表插入FROMfrom_statementINSERTOVERWRITE|INTOTABLEtablename1[PARTITION(partcol1=val1,partcol2=val2...)]select_statement1[INSERTOVERWRITE|INTOTABLEtablename2[PARTITION...]select_statement2]…OVERWRITE：采用覆盖方式插入INTO：采用追加方式插入数据导入-INSERT数据导入-INSERT插入动态分区表开启动态分区功能sethive.exec.dynamic.partition=true允许所有分区都是动态的sethive.exec.dynamic.partition.mode=nonstrict每个mapper或reducer可以动态创建的最大分区数量，默认100sethive.exec.max.dynamic.partitions.pernode=200一个动态分区创建语句可以创建的最大动态分区个数，默认1000sethive.exec.max.dynamic.partitions=500

数据导入-INSERT插入动态分区表语法INSERTOVERWRITE|INTOTABLEtablenamePARTITION(partcol1[=val1],partcol2[=val2]...)select_statementFROMfrom_statement;数据导入-INSERT插入动态分区表

数据导入-INSERT从一个表导出到一个输出目录INSERTOVERWRITE[LOCAL]DIRECTORYdirectory1[ROWFORMATrow_format][STOREDASfile_format]SELECT...FROM...从一个表导出到多个输出目录FROMfrom_statementINSERTOVERWRITE[LOCAL]DIRECTORYdirectory1select_statement1[INSERTOVERWRITE[LOCAL]DIRECTORYdirectory2select_statement2]...数据导出-INSERT数据导出-INSERT语法INSERTINTOTABLEtablename[PARTITION(partcol1[=val1],partcol2[=val