hive初使用(part 1)
5680 ワード
1. create table
目盛線を作成し、区切り文字と保存形式を指定します.
create table tmp_2 (resource_id bigint ,v int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\,'
LINES TERMINATED BY ''
STORED AS TEXTFILE;
//ROW FORMAT DELIMITED FIELDS TERMINTED BY','---ここではフィールド間をカンマで区切るように設定します.
//LINESTERMINATED BY'---行と行の間を改行で区切る
//STORED AS TEXTFILE---指定ファイルはtext形式で保存する;
Hiveにはデフォルトで3つのファイル形式TextFile,SequenceFileおよびRCFileがあります
//表作成時に「PARTITIOONED BY(file STRING)」でパーティションフィールドを指定することもできます
drop table if exists sa_base_order;create table IF NOT EXISTS sa_base_order...
上記の2つの判断を用いて、テーブルがすでに存在するerrorを作成しないようにすることができる.
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,ip STRING COMMENT 'IP Address of the User',country STRING COMMENT 'country of origination')COMMENT 'This is the staging page view table'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'STORED AS TEXTFILELOCATION '';
2.分析結果を目標表にインポートする
hivesqlは「insert overwrite table...」で、overwriteは書き換えファイルを上書きすることを示します
3.ODPS(アリビッグデータプラットフォーム)&HIVE
1)
dateaddとdate_sub
dateadd(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd'),-400,'dd'),' ',1)
DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),400)
2)
split_partとsplit
split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',1)
split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')AS STRING), '-')[0]
--odps
SELECT '@@{yyyy-MM-dd}', -- time(2015-12-02) DATEADD(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd'),-400,'dd'), -- 2014-10-28 split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',1), -- 2015 split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',2), -- 12 split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',3) -- 02 FROM DUAL;
--hive
USE test;SELECT UNIX_TIMESTAMP(),--unix time(2015-12-02),秒単位DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),400),--2014-10-28 split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')AS STRING), '-')[0], -- 2015 split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')AS STRING), '-')[1], -- 12 split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')AS STRING), '-')[2] -- 02 FROM DUAL;
4.lateral view使用
split,explodeなどのUDTFと併用し,1行のデータを複数行のデータ(UDTF)に分割し,その上で分割したデータを集約(仮想テーブル)することができる.
lateral viewはまず元のテーブルの各行にUDTFを呼び出し、UTDFは1行を1行または複数行に分割し、lateral viewは結果を組み合わせて、別名テーブルをサポートする仮想テーブルを生成する.
test
cids ,1000004525,215937867,1015096900,
dt 2014-12-12
,1,9,
2015-12-12
lateral view explode(split(cids,','))t as cidを使用して、元のテーブルのcidsを分割して新しい仮想テーブルを生成します.フィールドはcidです.
1000004525
2014-12-12
215937867
2014-12-12
1015096900
2014-12-12
1
2015-12-12
9
2015-12-12
1つのFROM文の後に複数のlateral view文を含めることができ、後のlateral view文は前のすべてのテーブルとカラム名を参照することができます.
5.Hive関数にない機能
ある日は何曜日ですか.
pmod(datediff(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'), '1920-01-01') - 3, 7)
日付を月ごとに増減しますか?
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),31),--1月の先月初めのdate_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),1),--1月の先月末date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),--1月の2ヶ月前の月初めdate_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),32)--1月の2ヶ月前の月尾
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),31),--2月の2ヶ月前の月初めdate_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),--2月の2ヶ月前の月尾
現在の月の開始時間:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')-1)
先月の月初め:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP()))-1),
先月末:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP())))
2ヶ月前の月初め:
date_sub(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP()))),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))-1),
2ヶ月前の月末:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP())))
目盛線を作成し、区切り文字と保存形式を指定します.
create table tmp_2 (resource_id bigint ,v int)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\,'
LINES TERMINATED BY ''
STORED AS TEXTFILE;
//ROW FORMAT DELIMITED FIELDS TERMINTED BY','---ここではフィールド間をカンマで区切るように設定します.
//LINESTERMINATED BY'---行と行の間を改行で区切る
//STORED AS TEXTFILE---指定ファイルはtext形式で保存する;
Hiveにはデフォルトで3つのファイル形式TextFile,SequenceFileおよびRCFileがあります
//表作成時に「PARTITIOONED BY(file STRING)」でパーティションフィールドを指定することもできます
drop table if exists sa_base_order;create table IF NOT EXISTS sa_base_order...
上記の2つの判断を用いて、テーブルがすでに存在するerrorを作成しないようにすることができる.
CREATE EXTERNAL TABLE page_view(viewTime INT, userid BIGINT,
page_url STRING, referrer_url STRING,ip STRING COMMENT 'IP Address of the User',country STRING COMMENT 'country of origination')COMMENT 'This is the staging page view table'ROW FORMAT DELIMITED FIELDS TERMINATED BY '\054'STORED AS TEXTFILELOCATION '
2.分析結果を目標表にインポートする
hivesqlは「insert overwrite table...」で、overwriteは書き換えファイルを上書きすることを示します
3.ODPS(アリビッグデータプラットフォーム)&HIVE
1)
dateaddとdate_sub
dateadd(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd'),-400,'dd'),' ',1)
DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),400)
2)
split_partとsplit
split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',1)
split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')AS STRING), '-')[0]
--odps
SELECT '@@{yyyy-MM-dd}', -- time(2015-12-02) DATEADD(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd'),-400,'dd'), -- 2014-10-28 split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',1), -- 2015 split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',2), -- 12 split_part(CAST(to_date('@@{yyyy-MM-dd}','yyyy-mm-dd') AS STRING), '-',3) -- 02 FROM DUAL;
--hive
USE test;SELECT UNIX_TIMESTAMP(),--unix time(2015-12-02),秒単位DATE_SUB(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),400),--2014-10-28 split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')AS STRING), '-')[0], -- 2015 split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')AS STRING), '-')[1], -- 12 split(CAST(FROM_UNIXTIME(UNIX_TIMESTAMP() ,'yyyy-MM-dd')AS STRING), '-')[2] -- 02 FROM DUAL;
4.lateral view使用
split,explodeなどのUDTFと併用し,1行のデータを複数行のデータ(UDTF)に分割し,その上で分割したデータを集約(仮想テーブル)することができる.
lateral viewはまず元のテーブルの各行にUDTFを呼び出し、UTDFは1行を1行または複数行に分割し、lateral viewは結果を組み合わせて、別名テーブルをサポートする仮想テーブルを生成する.
test
cids ,1000004525,215937867,1015096900,
dt 2014-12-12
,1,9,
2015-12-12
lateral view explode(split(cids,','))t as cidを使用して、元のテーブルのcidsを分割して新しい仮想テーブルを生成します.フィールドはcidです.
SELECT cid,dt
FROM test LATERAL VIEW explode(cids) test_new AS cid;1000004525
2014-12-12
215937867
2014-12-12
1015096900
2014-12-12
1
2015-12-12
9
2015-12-12
1つのFROM文の後に複数のlateral view文を含めることができ、後のlateral view文は前のすべてのテーブルとカラム名を参照することができます.
SELECT cid,dt1 FROM test LATERAL VIEW explode(dt) test1 AS dt1 LATERAL VIEW explode(cids) test2 AS cid;5.Hive関数にない機能
ある日は何曜日ですか.
pmod(datediff(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'), '1920-01-01') - 3, 7)
日付を月ごとに増減しますか?
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),31),--1月の先月初めのdate_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),1),--1月の先月末date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),--1月の2ヶ月前の月初めdate_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),32)--1月の2ヶ月前の月尾
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),31),--2月の2ヶ月前の月初めdate_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-01-01'),--2月の2ヶ月前の月尾
現在の月の開始時間:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd')-1)
先月の月初め:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP()))-1),
先月末:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP())))
2ヶ月前の月初め:
date_sub(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP()))),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))-1),
2ヶ月前の月末:
date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(date_sub(FROM_UNIXTIME(UNIX_TIMESTAMP(),'yyyy-MM-dd'),day(FROM_UNIXTIME(UNIX_TIMESTAMP()))))+day(FROM_UNIXTIME(UNIX_TIMESTAMP())))