Hiveゼロベース入門から実戦入門編(二十一)HiveQL:データ挿入

目次
 
前言
1.CREATE TABLE表名AS SELECT…
2. INSERT
2.1パーティション表なしでデータを挿入する
2.2パーティション表挿入データ
2.2.1 静的パーティション挿入
2.2.2ダイナミックパーティション挿入
2.2.3 静的パーティションと動的パーティションの混在
 
前言
入門編(10)では、ファイル内のデータをテーブルにインポートする方法について説明します.しかし、実際の作業では、クエリーされたデータをテーブルに挿入するのが一般的です.この操作を実現するには2つの方法があります.1:CREATE TABLEテーブル名AS SELECT......文を使用します.2:INSERT文を使用します.以下では、この2つの文の使い方をそれぞれ詳しく説明します.
 
1.CREATE TABLE表名AS SELECT…
この文の意味は、表を作成し、後のSELECT文でクエリーされたデータをこの新しく作成された表に挿入し、フィールド名とフィールドデータ型はすべてクエリーされたデータと一致します.
 
1.1構文

CREATE TABLE   1 AS
SELECT *
FROM   2;

後続のselect文では、すべての合法的なクエリー文を使用できますが、1つのHQL文しか使用できません.したがって、複雑なクエリーでは複数のサブクエリーがネストされる必要があります.また、この文ではwith as文の使用はサポートされていません.したがって、論理が複雑な場合は読み取りにくいです.
 
1.2シーンの適用
データの要件が複雑な場合は、論理的に簡単な中間テンポラリ・テーブルを作成します.この文を使用すると便利です.
 
1.3例
ここでは、以前のブログの文を使用して説明します.文は次のとおりです.

CREATE TABLE temp_test AS
SELECT user_id
      ,use_cnt
FROM app.t_od_use_cnt
WHERE date_8 = 20190101 limit 5;

 実行後に新しいテーブルtemp_をクエリーtest、実行結果は以下の通りです.

hive (app)> select * from temp_test;
OK
temp_test.user_id	temp_test.use_cnt
10004	29
10003	1
10002	23
10001	49
10000	6
Time taken: 0.085 seconds, Fetched: 5 row(s)

2. INSERT
INSERT文では、クエリーされたデータを作成済みのテーブルに挿入できます.クエリーされたデータの列の順序は、既存のテーブルの列の順序と一致する必要があります.
2.1パーティション表なしでデータを挿入する
パーティションテーブルなしでデータを挿入する操作は、一般的には、パーティションを必要としないのは一時テーブルのみです. 
2.1.1文法
データ上書きテーブルの既存データの挿入

INSERT overwrite TABLE   
SELECT * FROM ……; 

挿入データは表にあるデータの後に追加され、元のデータは削除されません.

INSERT INTO TABLE   
SELECT * FROM ……; 

ここでのクエリ文は同じように1つのHQL文しか使用できないため、複雑なクエリでは複数のサブクエリがネストされる必要がありますが、with as文の使用はサポートされています.
WITH AS文を使用する場合の構文は次のとおりです.

WITH a
AS (……)
      ,b
AS (……)
……
INSERT into | overwrite TABLE   
SELECT ……;

WITH AS文の最後のSELECT文をINSERT文の後に置けばよい.
 
2.1.2適用シーン
作成する必要があるテンポラリ・ミドル・テーブルの論理が複雑で、サブクエリの読み取りが悪い場合は、まずテンポラリ・テーブルを作成し、WITH AS......INSERT文を使用してクエリの結果として得られたデータをテーブルに挿入します.
 
2.1.3例
ここでは、上記のtemp_について説明します.testテーブルに10個のデータを挿入し、前のデータを上書きします.ここでは2つの文を使用してプレゼンテーションを行います.効果は同じです.

直接挿入

INSERT overwrite TABLE temp_test
SELECT user_id
      ,use_cnt
FROM app.t_od_use_cnt
WHERE date_8 = 20190101 limit 10;

WITH AS挿入

WITH a
AS (
      SELECT user_id
            ,use_cnt
      FROM app.t_od_use_cnt
      WHERE date_8 = 20190101 limit 10
      )
INSERT overwrite TABLE temp_test
SELECT *
FROM a;

クエリーテーブルtemp_test、次のように実行します.

hive (app)> select * from temp_test; 
OK
temp_test.user_id	temp_test.use_cnt
10009	40
10008	25
10007	40
10006	12
10005	15
10004	29
10003	1
10002	23
10001	49
10000	6
Time taken: 0.075 seconds, Fetched: 10 row(s)

 以前のデータが上書きされ、10個のデータが再挿入されたことがわかります.
 
2.2パーティション表挿入データ
前述のブログでは、HiveはHDFSに格納されており、Hiveのパーティション名はディレクトリ名に対応し、サブパーティション名はサブディレクトリ名であり、パーティションフィールドは実際のフィールドではありません.Hiveでパーティションテーブルにデータを挿入するには、静的パーティション挿入と動的パーティション挿入の2つの操作があります.以下では、それぞれ詳しく説明します.
2.2.1 静的パーティション挿入
2.2.1.1文法:

[WITH a
AS (……)
      ,b
AS (……)
……]
INSERT into | overwrite TABLE    PARTITION(    1='xxx',    2='xxx'……)
SELECT ……;

静的パーティションとはINSERT文の後に挿入するパーティションの値を指定し、キーワードpartitionを書かずに直接挿入すると、エラーが発生します.
パーティションフィールドはいくつかありますが、挿入時にいくつかのパーティションを指定しなければなりません.そうしないと、エラーが発生します.
SELECT後のフィールドにパーティションフィールドを含めることはできません.さもないと間違いを報告します.
パーティションフィールドのデータ型がSTRINGの場合は、コンテンツを'''で囲み、数値型の場合は'''を必要としません.
 
2.2.1.2 適用シーン:
静的パーティション挿入データは、通常、日常的なレポートの定例化操作で使用され、毎日スクリプトで昨日のデータを実行し、昨日のパーティションに挿入することを指定します.
 
2.2.1.3 例:
パーティションテーブルを作成するtemp_test 1、文は次のとおりです.

CREATE TABLE temp_test1 (
      user_id BIGINT comment '  id'
      ,use_cnt INT comment '      '
      ) partitioned BY (
      date_8 INT
      ,hour INT
      );

パーティションテーブルにデータを挿入するキーワードpartitionを書かずに直接挿入すると、次のようにエラーが発生します:

hive (app)> 
          > WITH a
          > AS (
          >       SELECT user_id
          >             ,use_cnt
          >       FROM app.t_od_use_cnt
          >       WHERE date_8 = 20190101 limit 10
          >       )
          > INSERT overwrite TABLE temp_test1
          > SELECT *
          > FROM a;
FAILED: SemanticException 8:23 Need to specify partition columns because the destination table is partitioned. Error encountered near token 'temp_test1'
hive (app)> 

dateのみ指定した場合8またはhourのいずれかのパーティションがデータを挿入すると、次のようにエラーが表示されます.

hive (app)> WITH a
          > AS (
          >       SELECT user_id
          >             ,use_cnt
          >       FROM app.t_od_use_cnt
          >       WHERE date_8 = 20190101 limit 10
          >       )
          > INSERT overwrite TABLE temp_test1 PARTITION(date_8=20190101) 
          > SELECT *
          > FROM a;
FAILED: SemanticException [Error 10006]: Line 8:44 Partition not found '20190101'
hive (app)> 

hive (app)> WITH a
          > AS (
          >       SELECT user_id
          >             ,use_cnt
          >       FROM app.t_od_use_cnt
          >       WHERE date_8 = 20190101 limit 10
          >       )
          > INSERT overwrite TABLE temp_test1 PARTITION(hour=1)
          > SELECT *
          > FROM a;
FAILED: SemanticException Line 0:-1 Partition not found '1'
hive (app)> 

 正しい書き方:

INSERT overwrite TABLE temp_test1 PARTITION (
      date_8 = 20190101
      ,hour = 1
      )
SELECT user_id
      ,use_cnt
FROM app.t_od_use_cnt
WHERE date_8 = 20190101 limit 10;

WITH a
AS (
      SELECT user_id
            ,use_cnt
      FROM app.t_od_use_cnt
      WHERE date_8 = 20190101 limit 10
      )
INSERT overwrite TABLE temp_test1 PARTITION (
      date_8 = 20190101
      ,hour = 1
      )
SELECT *
FROM a;

 両者の効果は一致する.
 クエリーテーブルtemp_test 1、運転効果は以下の通りです.

hive (app)> select * from temp_test1 where date_8=20190101 and hour=1; 
OK
temp_test1.user_id	temp_test1.use_cnt	temp_test1.date_8	temp_test1.hour
10009	40	20190101	1
10008	25	20190101	1
10007	40	20190101	1
10006	12	20190101	1
10005	15	20190101	1
10004	29	20190101	1
10003	1	20190101	1
10002	23	20190101	1
10001	49	20190101	1
10000	6	20190101	1
Time taken: 0.34 seconds, Fetched: 10 row(s)

hive (app)> select * from temp_test1 where date_8=20190101; 
OK
temp_test1.user_id	temp_test1.use_cnt	temp_test1.date_8	temp_test1.hour
10009	40	20190101	1
10008	25	20190101	1
10007	40	20190101	1
10006	12	20190101	1
10005	15	20190101	1
10004	29	20190101	1
10003	1	20190101	1
10002	23	20190101	1
10001	49	20190101	1
10000	6	20190101	1
Time taken: 0.112 seconds, Fetched: 10 row(s)

hive (app)> select * from temp_test1 where hour=1; 
OK
temp_test1.user_id	temp_test1.use_cnt	temp_test1.date_8	temp_test1.hour
10009	40	20190101	1
10008	25	20190101	1
10007	40	20190101	1
10006	12	20190101	1
10005	15	20190101	1
10004	29	20190101	1
10003	1	20190101	1
10002	23	20190101	1
10001	49	20190101	1
10000	6	20190101	1
Time taken: 0.073 seconds, Fetched: 10 row(s)

パーティションテーブルをクエリーするときに、パーティションの1つを指定してもエラーは報告されませんが、挿入するときはすべて指定する必要があります.
 
2.2.2ダイナミックパーティション挿入
hiveダイナミックパーティションを有効にするには、まずhiveセッションで2つのパラメータを設定する必要があります.

        ，   ：false
set hive.exec.dynamic.partition=true;

strict              ，                 ，   ：strict
set hive.exec.dynamic.partition.mode=nonstrict;

また、挿入するパーティションが100個以上1000個未満の場合は、次のパラメータを変更する必要があります.そうしないと、reduceフェーズに実行するとエラーが発生します.

         mapper  reducer        ，    100，     1000
set hive.exec.max.dynamic.partitions.pernode=1000;

挿入するパーティションが1000個より大きい場合は、次のパラメータを変更する必要があります.そうしないと、reduceフェーズに実行するとエラーが発生します.

                   ，    100，              。
set hive.exec.max.dynamic.partitions.pernode=2000;

         DML                ，    1000，             。
set hive.exec.max.dynamic.partitions=2000

 
2.2.2.1文法:

[WITH a
AS (……)
      ,b
AS (……)
……]
INSERT into | overwrite TABLE    PARTITION(    1,    2……)
SELECT ……    1,    2…… 
FROM ……;

 ダイナミックパーティションは、データを挿入するときにパーティションフィールド名を書くだけで、特定のパーティションは指定されません.次に、次のクエリの最後のフィールドにダイナミックパーティションのフィールドが含まれている必要があります.順序は、PARTITION後のパーティションフィールドと完全に一致している必要があります.
hiveは、selectの最後のいくつかの場所のパーティションフィールドのパラメータ値を取得し、insert文partitionのいくつかのパーティションフィールドに入力します.すなわち、ダイナミックパーティションは、位置によってパーティション値に対応します.元のテーブルselectから出た値と出力partitionの値の関係は位置だけで確定され、名前とは関係ありません!
 
2.2.2.2適用シーン:
動的パーティション挿入データは、過去30日間の毎日のデータを日別パーティションのテーブルに挿入するなど、定例化されたテーブルの遡及データに多く使用されます.静的パーティション遡及データを使用する場合は、スクリプトを30回実行する必要がありますが、動的パーティションを使用すると、一度にすべて挿入できます.
 
2.2.2.3例:

パラメータを設定せずにダイナミックパーティションを使用すると、

とエラーが発生します.

hive (app)> INSERT overwrite TABLE temp_test1 PARTITION (
          >       date_8
          >       ,hour
          >       )
          > SELECT user_id
          >       ,use_cnt
          >       ,date_8
          >       ,12 hour
          > FROM app.t_od_use_cnt
          > WHERE date_8 = 20190102 limit 10;
FAILED: SemanticException [Error 10096]: Dynamic partition strict mode requires at least one static partition column. To turn this off set hive.exec.dynamic.partition.mode=nonstrict

パーティションフィールドを1つもクエリーしないと、

とエラーが発生します.

hive (app)> INSERT overwrite TABLE temp_test1 PARTITION (
          >       date_8
          >       ,hour
          >       )
          > SELECT user_id
          >       ,use_cnt
          >       ,date_8
          > FROM app.t_od_use_cnt
          > WHERE date_8 = 20190102 limit 10;
FAILED: SemanticException [Error 10044]: Line 1:23 Cannot insert into target table because column number/types are different 'hour': Table insclause-0 has 4 columns, but query has 3 columns.

順序が間違っていれば、コードは間違っていませんが、挿入されたデータは間違っています.

INSERT overwrite TABLE temp_test1 PARTITION (
      date_8
      ,hour
      )
SELECT user_id
      ,use_cnt
      ,12 hour
      ,date_8
FROM app.t_od_use_cnt
WHERE date_8 = 20190102 limit 10;

クエリー・テーブルのデータは、フィールドはすべて存在しますが、最後の2つのパーティション・フィールドの内容が間違っていることがわかります.

hive (app)> select * from temp_test1;
OK
temp_test1.user_id	temp_test1.use_cnt	temp_test1.date_8	temp_test1.hour
10209	39	12	20190102
10208	26	12	20190102
10207	46	12	20190102
10206	39	12	20190102
10205	47	12	20190102
10204	30	12	20190102
10203	18	12	20190102
10202	31	12	20190102
10201	13	12	20190102
10200	46	12	20190102

 
正しい書き方:
まずパラメータを設定します.これらの設定は、今回のセッションでのみ有効になります.永続的に有効にするには、.hivercファイルに書き込むことができます.詳細は、入門編(11)を参照してください.次に、ダイナミックパーティションを使用してテーブルtemp_に向かいます.test 1にデータを挿入します.ここでは表t_od_use_cntにはhourフィールドがないので、hourは直接人のために12を設定します.

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT overwrite TABLE temp_test1 PARTITION (
      date_8
      ,hour
      )
SELECT user_id
      ,use_cnt
      ,date_8
      ,12 hour
FROM app.t_od_use_cnt
WHERE date_8 = 20190102 limit 10;

クエリーパーティションdate_8=20190102、hour=12のデータ:

hive (app)> select * from temp_test1 where date_8=20190102 and hour=12;
OK
temp_test1.user_id	temp_test1.use_cnt	temp_test1.date_8	temp_test1.hour
10209	39	20190102	12
10208	26	20190102	12
10207	46	20190102	12
10206	39	20190102	12
10205	47	20190102	12
10204	30	20190102	12
10203	18	20190102	12
10202	31	20190102	12
10201	13	20190102	12
10200	46	20190102	12
Time taken: 0.077 seconds, Fetched: 10 row(s)

2.2.3 静的パーティションと動的パーティションの混在
静的パーティションと動的パーティションを混合して使用する場合、静的パーティション値は動的パーティション値の前にある必要があります.そうしないと、エラーが発生します.
例:
動的パーティション値が静的パーティション値の前にある必要がある場合は、次のエラーが表示されます.

hive (app)> set hive.exec.dynamic.partition=true;
hive (app)> set hive.exec.dynamic.partition.mode=nonstrict;
hive (app)> INSERT overwrite TABLE temp_test1 PARTITION (
          >       date_8
          >       ,hour=1
          >       )
          > SELECT user_id
          >       ,use_cnt
          >       ,12 hour
          > FROM app.t_od_use_cnt
          > WHERE date_8 = 20190101 limit 10;
FAILED: SemanticException [Error 10094]: Line 2:6 Dynamic partition cannot be the parent of a static partition '1'

正しい書き方: 

set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
INSERT overwrite TABLE temp_test1 PARTITION (
      date_8=20190101
      ,hour
      )
SELECT user_id
      ,use_cnt
      ,12 hour
FROM app.t_od_use_cnt
WHERE date_8 = 20190101 limit 10;

 
ここが见える人は、右上にいいねをつけて私に注目してください.3 Q~