前端之家

  1. 首页
  2. 问答

了解Hive表创建符号

• 问答

我遇到过Hive表，我需要将其转换为Redshift / MySql等效项。 我在理解Hive查询结构时遇到麻烦，希望能提供一些帮助：

CREATE TABLE IF NOT EXISTS table_1 (
    id BIGINT,price DOUBLE,asset string
)
PARTITIONED BY (
    pt STRING
);
ALTER TABLE table_1 DROP IF EXISTS PARTITION (pt== '${yyyymmdd}');

INSERT OVERWRITE TABLE table_1 PARTITION (pt= '${yyyymmdd}') 
select aa.id,aa.price,aa.symbol from
...
...
from
 table_2 table

我无法理解PARTITIONED BY子句。如果我正确理解的话，这与MySQL表分区不同，并且是Hive特定的动态分区。 分区未定义列或键，而是按当前日期进行分区。

这是否意味着table_1按日期进行了分区？每天都有一个单独的分区吗？

然后在代码中稍后会有类似于

的符号 
inner join table_new table on table.pt = '${yyyymmdd}' and ...

在这种情况下，这是否意味着仅选择插入yyyymmdd上的行进行联接？

谢谢。

iCMS 回答：了解Hive表创建符号

Hive中的分区默认是HDFS中的文件夹，名称为key=value + Hive元存储中的元数据。您可以更改分区位置并在任何文件夹顶部创建分区。

此PARTITIONED BY (pt STRING)定义了类型为 string 而不是日期的分区列pt。分区值存储在元数据中。 pt列不在表数据文件中，它仅在PARTITIONED BY中定义，所有分区值都存储在元数据中。如果动态加载分区，则将使用名称pt ='value'创建分区文件夹。

这句话动态创建分区：

INSERT OVERWRITE TABLE table_1 PARTITION (pt) 
select id,price,symbol
       coln as pt            --partition column should be the last one
  from ...

这句话将加载单个STATIC分区：

INSERT OVERWRITE TABLE table_1 PARTITION (pt= '${yyyymmdd}') 
select aa.id,aa.price,aa.symbol 
  from

未选择分区列，在

中指定了分区值 
PARTITION  (pt= '${yyyymmdd}')

'${yyyymmdd}'这是一个名称为yyyymmdd的参数，该参数使用--hivevar传递给脚本，如下所示：

 hive --hivevar yyyymmdd=20200604 -f myscript.sql

在这种情况下，尽管参数名称yyyymmdd建议使用其格式，您也可以将ANY字符串作为分区值传递。

配置单元中的BTW日期格式为'yyyy-MM-dd'，'yyyy-MM-dd'格式的字符串可以隐式转换为DATE。

,

我将尝试解释一下Hive中的分区。首先是

何时使用表分区

  • 在以下情况下，表partitioninig很好：

    • 读取整个数据集会花费很长时间
    • 查询几乎总是在分区列上进行过滤
    • 分区列有相当数量的不同值

  • ETL过程的数据生成按文件或目录名称拆分数据

  • 分区列值不在数据本身中
  • 不要在具有许多唯一值的列上分区
  • 示例：按名字对客户进行分区

创建分区表

要创建分区表，请在CREATE TABLE语句中使用PARTITIONED BY子句。 必须指定分区列的名称和类型 在PARTITIONED BY子句中，并且仅在PARTITIONED BY子句中。 它们也不能出现在所有其他列的列表中。

CREATE TABLE customers_by_country 
        (cust_id STRING,name STRING) 
PARTITIONED BY (country STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

上面显示的示例CREATE TABLE语句创建表customer_by_country， 它由名为国家的STRING列划分。 请注意，“国家/地区”列仅出现在PARTITIONED BY子句中， 而不是在其上方的列列表中。 本示例仅指定一个分区列，但是您可以使用来指定多个分区列 PARTITIONED BY子句中以逗号分隔的列列表。 除了这些特定的差异外，此CREATE TABLE语句相同 作为用于创建等效的非分区表的语句。

以最透明的方式实现表分区 向使用Hive发出查询的用户。 分区列称为虚拟列，因为其值未存储在数据文件中。 以下是对customers_by_country的DESCRIBE命令的结果； 它显示分区列国家/地区，就好像它是表中的普通列一样。 您可以在SELECT语句的任何常用子句中引用分区列。

name    type    comment

cust_id string   
name    string   
country string

您可以动态或静态地将数据加载到分区表中

以动态分区加载数据

将数据加载到分区表中的一种方法是使用动态分区， 当您加载数据时，它会使用partition列中的值自动定义分区。 （另一种方法是使用“静态分区”手动定义分区）

要使用动态分区，必须使用INSERT语句加载数据。 在INSERT语句中，必须使用PARTITION子句列出分区列。 您要插入的数据必须包含分区列的值。 分区列必须是要插入的数据中最右边的列， 并且它们必须与PARTITION子句中出现的顺序相同。

INSERT OVERWRITE TABLE customers_by_country 
    PARTITION(country)
    SELECT cust_id,name,country FROM customers;

上面显示的示例使用INSERT…SELECT语句 通过动态分区将数据加载到customer_by_country表中。 请注意，分区列包括国家 在PARTITION子句中，并且在SELECT列表中最后指定。

Hive执行此语句时，它将自动创建分区 用于国家/地区列，并根据国家/地区列中的值将数据加载到这些分区中。 分区子目录中的结果数据文件不包含“国家/地区”列的值。 由于根据数据文件所在的子目录知道该国家/地区， 在数据文件中也包含国家/地区值是多余的。

查看customers_by_country目录的内容。 现在，“国家/地区”列中的每个值都应该有一个子目录。

  1. 在这些目录之一中查看文件。 请注意，该文件包含该国家/地区的客户行， 没有其他人；另请注意，国家/地区值不包括在内。

注意：Hive包含一项安全功能，可防止用户 避免意外创建或覆盖大量分区。 （有关更多信息，请参见“使用分区的风险”。） 默认情况下，Hive将属性hive.exec.dynamic.partition.mode设置为strict。 尽管您仍然可以使用静态分区，但这会阻止您使用动态分区。

您可以通过设置在Hive中禁用此安全功能 hive.exec.dynamic.partition.mode属性设为非严格限制：

SET hive.exec.dynamic.partition.mode=nonstrict;

然后，您可以使用INSERT语句动态加载数据。

在Beeline中设置的配置单元属性仅适用于当前会话， 因此，下次您启动Hive会话时，此属性将重新设置为strict。 但是您或您的系统管理员可以根据需要永久配置属性。

在分区表上运行一些SELECT查询时，如果表足够大，您会注意到运行时间的显着差异。 请注意，查询表的方式与查询客户表的方式相同。

通过静态分区加载数据

将数据加载到分区表中的一种方法是使用静态分区， 您可以在其中手动定义不同的分区。

对于静态分区，您可以使用ALTER TABLE…ADD PARTITION语句手动创建分区， 然后将数据加载到分区中。

例如，此ALTER TABLE语句为巴基斯坦（pk）创建分区：

ALTER TABLE customers_by_country
ADD PARTITION (country='pk');

请注意分区列名称（即国家/地区）以及定义此分区的特定值的方式， 都是pk，都在ADD PARTITION子句中指定。 这样会在customers_by_country表目录中创建一个名为country = pk的分区目录。

创建巴基斯坦分区后，可以使用INSERT…SELECT语句将数据添加到分区中：

INSERT OVERWRITE TABLE customers_by_country 
    PARTITION(country='pk')
    SELECT cust_id,name FROM customers WHERE country='pk'

请注意，如何在PARTITION子句中使用分区列名称（即国家/地区）， 都指定了特定值pk，就像在用于创建分区的ADD PARTITION命令中一样。 还要注意，在SELECT语句中，分区列不包括在SELECT列表中。 最后，请注意，SELECT语句中的WHERE子句仅选择来自巴基斯坦的客户。

对于静态分区，您需要为每个分区重复以下两个步骤： 首先创建分区，然后添加数据。 实际上，您可以使用任何方法来加载数据。您无需使用INSERT语句。 您可以改用hdfs dfs命令或LOAD DATA INPATH命令。 但是，无论您加载数据是什么，都有责任确保数据存储在正确的分区子目录中。 例如，巴基斯坦客户的数据必须存储在巴基斯坦分区子目录中， 并且其他国家/地区的客户数据必须存储在这些国家/地区的分区子目录中。

静态分区在加载数据时最有用 进入表已经根据分区列分为文件， 或当数据以与分区列一致的方式增长时： 例如，假设您的公司在其他国家/地区开设了一家新商店， 例如新西兰（'nz'），您将获得一份来自该国家/地区的新客户的数据文件。 您可以轻松添加新分区并将该文件加载到其中。

使用分区的风险

使用分区时的主要风险是创建分区，使您陷入小文件问题。 发生这种情况时，对表进行分区实际上会降低查询性能 （与使用分区时的目标相反），因为它会导致创建过多的小文件。 使用动态分区时，这种可能性更大，但仍然可以 静态分区发生—例如，如果您向销售表中添加了新分区 每天包含前一天的销售额， 而且每天的数据并不是特别大。

选择分区时，要在太多分区之间取得平衡 （导致小文件问题）和分区太少（提供性能几乎没有好处）。 一个或多个分区列应具有合理数量的值 分区-但是您应该认为合理的事情很难量化。

使用动态分区特别危险，因为如果您不小心， 在具有太多不同值的列上进行分区很容易。 想象一个用例，您经常寻找属于该范围的数据 您将在查询中指定的时间范围。 您可能会认为在与时间有关的列上进行分区是个好主意。 但是TIMESTAMP列的时间可以达到纳秒，因此每一行都可以具有唯一的值。 对于分区列，这将是一个糟糕的选择！即使是几分钟或几小时也可能造成 分区太多，具体取决于数据的性质； 按较大的时间单位（例如日，月甚至年）进行分区可能是一个更好的选择。

再举一个例子，考虑一个employees表。 它具有五列：empl_id，first_name，last_name，salary和office_id。 在继续阅读之前，请先考虑一下，其中哪个分区可能合理

  • 列empl_id是唯一标识符。 如果这是您的分区列，则每个员工都有一个单独的分区， 并且每行将只有一排。 此外，您不太可能会进行大量查询以寻找特定值， 甚至是特定范围的值。这是一个糟糕的选择。
  • first_name列中每个员工没有一个，但是很可能有许多列只有一行。
  • 对于last_name也是如此。 另外，像empl_id一样，您不太可能需要基于这些列的过滤器查询。这些也是错误的选择。
  • 专栏的薪水也会有很多划分 （如果您的薪水是美分而不是美元（如我们的示例表所示），则更是如此）。 尽管有时您可能想查询薪水范围， 您不太可能希望使用个人工资。 因此，薪水是一个糟糕的选择。
  • 一个更受限制的薪水等级规范，例如薪水等级表中的规范， 如果您的用例涉及经常按薪资等级查看数据，则可能是合理的。
  • office_id列标识员工工作的办公室。 即使您拥有在许多城市设有办事处的大型公司，这也将具有更少的独特价值。 可以想象您的用例可能是经常过滤 您的员工数据也基于办公室位置。因此，这将是一个不错的选择。 您还可以使用多列并创建嵌套分区。 例如，客户数据集可能包含country和state_or_province列。 您可以按国家划分分区，然后再按state_or_province划分分区，因此来自安大略省的客户 加拿大将位于country = ca /​​ state_or_province = on /分区目录中。 这对于要按国家或州或省访问的大量数据非常有用。 但是，使用多列会增加创建过多分区的危险，因此在执行此操作时必须格外小心。

创建过多分区的风险是为什么Hive包含该属性的原因 hive.exec.dynamic.partition.mode，默认情况下设置为严格，创建分区之前必须将其重置为非严格。

您将要动态加载数据时，不会自动机械地重置该属性， 借此机会思考分区列 并检查加载数据时获得的唯一值的数量。

仅此而已。

create-tablehivehive-partitionshiveddl
本文链接：https://www.f2er.com/2207283.html

大家都在问