parquet文件上传到hdfs_impala+hdfs+parquet格式文件

[创建目录]

hdfs dfs -mkdir -p /user/hdfs/sample_data/parquet

[赋予权限]

sudo -u hdfs hadoop fs -chown -R impala:supergroup /user/hdfs/sample_data

[删除目录]

hdfs dfs -rm -r /user/hdfs/sample_data/parquet

[上传文件]

hdfs dfs -put -f device /user/hdfs/sample_data/parquet

hdfs dfs -put -f metrics /user/hdfs/sample_data/parquet

[查看文件]

hdfs dfs -ls /user/hdfs/sample_data/parquet

[impala建表&＃xff0c;不带分区](创建表之后&＃xff0c;还需要通过下面的alter语句添加分区)

DROP TABLE IF EXISTS device_parquet;

CREATE EXTERNAL TABLE device_parquet

(

deviceId STRING,

deviceName STRING,

orgId STRING

)

STORED AS PARQUET

LOCATION &＃39;/user/hdfs/sample_data/parquet/device&＃39;;

[impala建表&＃xff0c;带分区]

DROP TABLE IF EXISTS metrics_parquet;

CREATE EXTERNAL TABLE metrics_parquet

(

deviceId STRING,

reading BIGINT,

time STRING

)

partitioned by (year string)

STORED AS PARQUET

LOCATION &＃39;/user/hdfs/sample_data/parquet/metrics&＃39;;

[添加表分区]

alter table metrics_parquet add partition (year&＃61;"2017");

alter table metrics_parquet add partition (year&＃61;"2018");

[删除分区]

alter table metrics_parquet drop partition (year&＃61;"2017");

alter table metrics_parquet drop partition (year&＃61;"2018");

[查看表分区]

show partitions metrics_parquet;

[不指定分区查询数据]

select

T_3C75F1.&＃96;deviceId&＃96;,

year(T_3C75F1.&＃96;time&＃96;),

month(T_3C75F1.&＃96;time&＃96;),

sum(T_3C75F1.&＃96;reading&＃96;),

count(1)

from (select device_parquet.deviceId,reading,metrics_parquet.time as time from device_parquet,metrics_parquet where device_parquet.deviceId&＃61;metrics_parquet.deviceId) as &＃96;T_3C75F1&＃96;

group by

T_3C75F1.&＃96;deviceId&＃96;,

year(T_3C75F1.&＃96;time&＃96;),

month(T_3C75F1.&＃96;time&＃96;);

耗时&＃xff1a;device表50条&＃xff0c;metrics表1亿条(261M)执行上面的查询语句&＃xff0c;耗时平均135秒

[指定分区查询数据]

select

T_3C75F1.&＃96;deviceId&＃96;,

year(T_3C75F1.&＃96;time&＃96;),

month(T_3C75F1.&＃96;time&＃96;),

sum(T_3C75F1.&＃96;reading&＃96;),

count(1)

from (select device_parquet.deviceId,reading,metrics_parquet.time as time from device_parquet,metrics_parquet where device_parquet.deviceId&＃61;metrics_parquet.deviceId and year&＃61;&＃39;2017&＃39;) as &＃96;T_3C75F1&＃96;

group by

T_3C75F1.&＃96;deviceId&＃96;,

year(T_3C75F1.&＃96;time&＃96;),

month(T_3C75F1.&＃96;time&＃96;);

耗时&＃xff1a;device表50条&＃xff0c;metrics表1亿条(261M)执行上面的查询语句&＃xff0c;耗时平均96秒

[查询多个分区的数据]

select

T_3C75F1.&＃96;deviceId&＃96;,

year(T_3C75F1.&＃96;time&＃96;),

month(T_3C75F1.&＃96;time&＃96;),

sum(T_3C75F1.&＃96;reading&＃96;),

count(1)

from (select device_parquet.deviceId,reading,metrics_parquet.time as time from device_parquet,metrics_parquet where device_parquet.deviceId&＃61;metrics_parquet.deviceId and year in (&＃39;2017&＃39;,&＃39;2018&＃39;)) as &＃96;T_3C75F1&＃96;

group by

T_3C75F1.&＃96;deviceId&＃96;,

year(T_3C75F1.&＃96;time&＃96;),

month(T_3C75F1.&＃96;time&＃96;);

[刷新数据](hdfs中数据发生变化时&＃xff0c;需要执行以下命令更新impala)

refresh device_parquet;

refresh metrics_parquet;