hive学习笔记4——建表要求+表类型

建表要求一、两类建表的完整语法1.标准建表CREATE[TEMPORARY][EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_name[(字段定义...约束注释)][COMMENT表注释][PARTITIONEDBY分区][CLUSTEREDBY分桶排序][SKEWEDBY数据倾斜优化][ROWFORMAT 行分隔规则][STOREDAS/STOREDBY存储格式][LOCATION hdfs路径][TBLPROPERTIES 表属性][AS查询语句];关键字释义[TEMPORARY]临时表会话结束自动销毁[EXTERNAL ]外部表删表只删元数据HDFS 原始文件保留不加就是内部托管表删表文件一起删除[IF NOT EXISTS] 表不存在才创建避免重复建表报错[db_name.table_name]指定数据库 表名省略则用当前库[AS select_statement]建表并直接把查询结果插入表外部表不能用2.复制表结构建表LIKE只复制结构不带数据CREATE[TEMPORARY][EXTERNAL]TABLE[IFNOTEXISTS][db_name.]table_nameLIKE已有表/视图名[LOCATION hdfs路径];作用完全复刻原表字段、分区、分桶、存储格式不复制数据可自定义新表存储路径。二. ROW FORMAT 行数据格式控制文本怎么分割1.两种写法用来定义文件里字段、数组、map 之间的分隔符ROWFORMAT DELIMITEDFIELDSTERMINATEDBY\t-- 字段之间分隔符COLLECTION ITEMSTERMINATEDBY,-- 数组/struct内部元素分隔符MAPKEYSTERMINATEDBY:-- map的key和value分隔符LINESTERMINATEDBY\n-- 每条记录换行符NULLDEFINEDASchar-- 空值用什么字符表示2.serdeserde_name with serderoperopertles 自定义序列化器用于 JSON、CSV、自定义格式文件通过 SERDE 解析数据三、STORED 文件存储格式 file_format定义表底层 HDFS 文件格式影响查询性能、压缩[TEXTFILE]默认文本文件可读性高性能差[SEQUENCEFILE]二进制序列文件[RCFILE]列式老式存储[ORC]主流高性能列式存储数仓首选压缩好、查询快[PARQUET]通用列式存储兼容 Spark/Hive[AVRO]适合数据同步、序列化[JSONFILE]JSON 格式文件Hive4.0[INPUTFORMAT OUTPUTFORMAT]自定义读写文件 Java 类四、各类优化分层句子PARTITIONED BY 分区按指定字段分文件夹存储数据缩小查询扫描范围如按 dt 日期分区PARTITIONEDBY(dt STRINGCOMMENT日期分区)2.CLUSTERED BY … SORTED BY … INTO N BUCKETS 分桶按字段哈希打散到固定数量文件配合 join、抽样查询提速SORTED BY 桶内数据自动排序3. SKEWED BY 倾斜优化针对某几个值数据量超大数据倾斜单独存目录减少查询卡顿STORED AS DIRECTORIES倾斜数据单独存独立文件夹4. LOCATION自定义表在 HDFS 的存储路径外部表常用不写则使用仓库默认路径5. TBLPROPERTIES自定义表属性比如开启压缩、设置生命周期、注释、读写参数等TBLPROPERTIES(orc.compresssnappy)COMMENT给表、字段、分区字段加中文注释方便维护看懂表业务含义7.整体逻辑总结①先选建表方式普通建表 / LIKE 复制结构建表②定义表属性临时 / 外部 / 内部表、库名表名、表注释③定义字段字段名 数据类型 字段注释④分层优化配置分区 → 分桶 → 倾斜优化⑤数据读写配置行分隔 ROW FORMAT → 文件存储 STORED AS⑥附加配置HDFS 路径 LOCATION、自定义表参数 TBLPROPERTIES表类型1.内容包含内部表外部表分区表分桶表2.内部表 MANAGED_TABLE内部表完全(元数据[MySQL]数据文件[HDFS])由hive自己管理删除内部表, 会同时删除 元数据 数据文件3.外部表 EXTERNAL_TABLE外部表的元数据由hive 管理, 数据文件被HDFS 管理删除内部表, 只删除 元数据 , 数据文件保留相当于内部表比较安全4.分区表Hive 分区表是一种优化 Hive 查询方法用以提高查询性能。分区表通过将数据分散到不同的分区中使得查询仅分区数据而不是整个表从而减少了数据扫描的范围createtabletrade(city string,amtint)partitionedby(sf string)-- 指定分区字段和类型ROWFORMAT DELIMITEDFIELDSTERMINATEDBY,-- 添加分区方式1altertabletradeaddpartition(sfshandong)altertabletradeaddpartition(sfhenan)select*fromtradewheresfshandong-- 添加分区方式2(常用方式)--将外部的分区文件夹和数据上传(put)到 表中, 该分区数据 没有 元数据msck repairtabletrade-- 给分区添加 元数据5.动态分区表根据设置的分区字段, 自动创建对应的分区-1.需要创建一张临时表,存储全部的数据createtablecity(province_id string,city_id string,amountint,city_name string)ROWFORMAT DELIMITEDFIELDSTERMINATEDBY,;--2. 上传数据到 临时表--3. 开启hive 支持 动态分区的参数sethive.exec.dynamic.partitiontruesethive.exec.dynamic.partition.modenonstrict--4. 创建一张分区表createtablecity_dtfq(province_id string,city_id string,amountint,city_name string)partitionedby(sf string)--5. 将数据从临时表导入到 分区表insertintocity_dtfqpartition(sf)selectprovince_id,city_id,amount,city_name,province_id-- 该字段的值 作为 sf 分区的值(sf分区根据province_id 列的值自动创建分区, 该字段必须放在最后)fromcity分桶表在Hive中对表进行分桶Bucketing是一种优化数据查询和数据存储的方法。分桶可以帮助提高某些类型查询的效率特别是在执行诸如JOIN操作时。分桶是通过将数据分散到多个文件中每个文件包含表中某一列或几列的相同值从而使得查询可以更快地定位到需要的数据。-- 1. 需要创建一张临时表, 存储全部的数据createtablecity(province_id string,city_id string,amountint,city_name string)ROWFORMAT DELIMITEDFIELDSTERMINATEDBY,;--2. 数据上传到临时表--3. 创建 分桶表 [ 指定分桶字段 分桶的数量]createtablecity_ft(province_id string,city_id string,amountint,city_name string)CLUSTEREDBY(province_id)INTO2BUCKETS--4. 设置支持分桶的参数sethive.enforce.bucketingtrue;-- 5. 导入数据insertintocity_ftselect*fromcity注项目应用上项目上事实表数据量庞大, 先分区 再分桶。