HQL的一些使用：分桶，分区

SQL（常用于数据库，存储数据）：通常用于关系型数据库管理系统（RDBMS）中，用于管理和查询结构化数据

HQL（常用于数据仓库，存储并分析数据）：它是一个在大数据生态系统中用于处理和分析大规模数据的数据仓库解决方案，用于查询和分析存储在分布式文件系统（如HDFS）中的非结构化或半结构化数据

---

 

前排贴一份Hive SQL语法大全：Hive SQL 语法大全，宇宙最强整理（建议收藏）

SQL和HQL在某些部分是共通的：HQL有分桶，分区（SQL也有），还可以创建外部表。

区分SQL和HQL，他们不是一个东西，下面展示HQL和SQL不同的部分细节

---

通用创建表（下面有细分）：

其中[]中的内容表示可选，()中的内容表示必填，这里只是一段代码，用————间隔开是为了直观和方便区分！请勿使用中文以免出现意外，示例中的中文只为了更好理解

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name　
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]

---

  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]

---

  [
   [ROW FORMAT row_format] 
   [STORED AS file_format]
     | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  ]
  [LOCATION hdfs_path]
  [TBLPROPERTIES (property_name=property_value, ...)]
  [AS select_statement];

 解释：

CREATE TABLE: 这是创建表格的关键字。

[TEMPORARY]: 可选项，用于指定是否创建临时表格。临时表格在当前会话结束后会被自动删除。

[EXTERNAL]: 可选项，用于指定是否创建外部表格。外部表格的数据存储在外部数据源中，而不在Hive的仓库中。

[IF NOT EXISTS]: 可选项，用于指定如果表格已经存在，则不会报错。

[db_name.]table_name: 表示表格的名称，可以包括数据库名称和表格名称。

col_name data_type [column_constraint_specification] [COMMENT col_comment]: 定义表格的列，包括列名、数据类型、列约束和列注释。

[COMMENT table_comment]: 可选项，用于为整个表格添加注释。

---

[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]: 可选项，用于定义分区键，将表格按照指定的列进行分区。

[CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]: 可选项，用于定义表格的分桶策略。分桶可以提高查询性能。

[SKEWED BY (col_name, col_name, ...)] ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)]: 可选项，用于定义数据倾斜处理策略。

[STORED AS DIRECTORIES]: 可选项，表示数据存储为目录结构。

---

[ROW FORMAT row_format]: 可选项，用于指定行的格式。

[STORED AS file_format] | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]: 用于指定数据的存储格式，可以是文件格式（如TEXTFILE、ORC、PARQUET）或自定义的存储处理器。

[LOCATION hdfs_path]: 可选项，用于指定数据存储的位置。

[TBLPROPERTIES (property_name=property_value, ...)]: 可选项，用于指定表格的属性。

[AS select_statement]: 可选项，用于从查询结果创建表格，即创建一个查询的结果集作为表格的内容。

 

创建外部表：

示例：

CREATE EXTERNAL TABLE IF NOT EXISTS my_hive (id INT,name STRING)　　-- 创建一个外部表，名字为my_hive，有一个int类型的id字段和string类型的name字段，使用IF NOT EXISTS指定如果表格已经存在，则不会报错
row format delimited 　　  -- 指定外部表格的行格式的关键词，后面跟参数
fields terminated by ','　　-- 字段的分隔格式
LINES TERMINATED BY '\n'　　-- 行之间的分隔格式
stored as textfile 　　    -- 指定行的数据存储格式
location '/user/hdfs/source/hive_test' ;   -- 指定数据存储的格式

 创建分区表：

示例：

create table IF NOT EXISTS test_part_table(  --创建一个表，指定表格存在不报错，名字为test_part_table，有俩字段，string的word，bigint的num
word string,
num bigint)
partitioned by(dt string)　　--　　定义分区键，名为dt，类型为string（不会物理提现，即查全表的时候不会体现在查询结构中）
row format delimited 　　--　　指定外部表格的行格式关键词，后面跟参数
fields terminated by '\t'　　--　　字段的分隔符
STORED AS TEXTFILE;　　--　　保存格式

创建分区外部表：

示例：

-- 基本同创建分区表逻辑
create external table IF NOT EXISTS log_detail(
word string,
num bigint)
partitioned by(dt string)
row format delimited 
fields terminated by '\t'
STORED AS TEXTFILE
location '/user/hdfs/source/hive_test' ;

创建桶表：

示例：

CREATE TABLE page_view(
     viewTime INT, 
     userid BIGINT,
     page_url STRING, 
     referrer_url STRING,
     ip STRING COMMENT 'IP Address of the User')
 COMMENT 'This is the page view table'　　-- 以上都是基本创表流程，不再复述
 PARTITIONED BY(dt STRING, country STRING)　　--　　分区，有两个关键字，dt和country，都是string类型
 CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS -- 定义了分桶策略。表格将按照 userid 列进行分桶，同时按照 viewTime 列进行排序。数据将分成32个分桶，数据会按照userid均匀地分到这32个桶中
 ROW FORMAT DELIMITED　　--　　指定外部表格式，后跟参数
   FIELDS TERMINATED BY '\001'
   COLLECTION ITEMS TERMINATED BY '\002'
   MAP KEYS TERMINATED BY '\003'
 STORED AS SEQUENCEFILE;　　--文件保存的格式

桶倾斜（指定哪些数据进入哪些桶，优化查询的）：

例子：

-- 这是创建一个没有倾斜的桶表，数据会被均匀的分配到四个桶中
CREATE TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING)
  CLUSTERED BY (col1, col2) INTO 4 BUCKETS;

-- 这是创建一个有倾斜的桶表，会根据col1和col2的值有选择的进入到桶中
CREATE TABLE list_bucket_multiple (col1 STRING, col2 int, col3 STRING)
  SKEWED BY (col1, col2) ON (('s1',1), ('s3',3), ('s13',13), ('s78',78));

-- 假设括号第一个值是col1的值，第二个是col2的值，桶的位置按上面ON后的顺序，('s1',1)为第一个桶，如此类推
('s3',1)：会进入第二个桶中

('s3',3)：会进入第二个桶中

('sss',3)：随机进桶

---

使用指定的序列化反序列类来读取行数据：

示例：

CREATE TABLE apachelog (
  host STRING,
  identity STRING,
  user STRING,
  time STRING,
  request STRING,
  status STRING,
  size STRING,
  referer STRING,
  agent STRING)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe'　　--这一行指定了表格的行格式，并且使用了一个特定的序列化/反序列化器（SerDe），后面跟着定义的SERDE属性
WITH SERDEPROPERTIES (
  "input.regex" = "([^]*) ([^]*) ([^]*) (-|\\[^\\]*\\]) ([^ \"]*|\"[^\"]*\") (-|[0-9]*) (-|[0-9]*)(?: ([^ \"]*|\".*\") ([^ \"]*|\".*\"))?"
)
STORED AS TEXTFILE;　　--指定了表格数据的存储格式