쿼리 파일로 쿼리 실행하기
파일에 쿼리를 작성하고 파일을 로딩하여 쿼리를 실행하기 위해서 다음의 커맨드를 사용할 수 있습니다.
# impala-shell.sh -i impala-host -f query.sql
Partition Key 지정하기
테이블 생성시 Partition Key를 지정하려면 다음과 같이 테이블을 생성합니다.
create table logs (
field1 string,
field2 string,
field3 string
)
partitioned by (year string, month string , day string, host string)
row format delimited fields terminated by ','
데이터를 INSERT할 때에는 다음과 같이 Partition Key를 지정할 수 있습니다.
insert into logs partition (year="2013", month="07", day="28", host="host1") values ("foo","foo","foo");
External Table 생성하기
Managed Table 대신 저장 위치를 별도로 지정하여 생성할 수 있는 External Table을 생성하려면 다음의 쿼리를 실행합니다.
create external table logs (
field1 string,
field2 string,
field3 string
)
partitioned by (year string, month string, day string, host string)
row format delimited fields terminated by ','
location '/user/impala/data/logs';
이 경우 새로운 파티션을 추가하기 위해서는 다음의 쿼리를 실행할 수 있습니다.
alter table logs add partition (year="2013",month="07",day="28",host="host1")
이제 생성한 파티션에 데이터를 적재합니다.
$ cat >dummy_log_data bar,baz,bletch $ hdfs dfs -put dummy_log_data /user/impala/data/logs/year=2013/month=07/day=28/host=host1
이제 마지막으로 Refresh를 실행합니다.
refresh log_type; select * from log_type limit 100; +--------+--------+--------+------+-------+-----+-------+ | field1 | field2 | field3 | year | month | day | host | +--------+--------+--------+------+-------+-----+-------+ | bar | baz | bletch | 2013 | 07 | 28 | host1 | | bar | baz | bletch | 2013 | 08 | 01 | host1 | | bar | baz | bletch | 2013 | 07 | 29 | host1 | | bar | baz | bletch | 2013 | 07 | 28 | host2 | +--------+--------+--------+------+-------+-----+-------+
Parquet 파일의 스키마에서 테이블 생성하기
이미 Parquet 파일이 있는 경우 동일한 스키마를 가진 새로운 테이블을 생성하려면 다음의 쿼리문을 실행합니다.
CREATE DATABASE airlines_data; USE airlines_data; CREATE EXTERNAL TABLE airlines_external LIKE PARQUET 'hdfs:staging/airlines/4345e5eef217aa1b-c8f16177f35fd983_1150363067_data.0.parq' STORED AS PARQUET LOCATION 'hdfs:staging/airlines'
DESCRIBE/SHOW 활용하기
> SHOW TABLE STATS airlines_external; +-------+--------+--------+--------------+-------------------+---------+-------------------+ | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format | Incremental stats | +-------+--------+--------+--------------+-------------------+---------+-------------------+ | -1 | 8 | 1.34GB | NOT CACHED | NOT CACHED | PARQUET | false | +-------+--------+--------+--------------+-------------------+---------+-------------------+ > SHOW FILES IN airlines_external; +----------------------------------------------------------------------------------------+----------+-----------+ | path | size | partition | +----------------------------------------------------------------------------------------+----------+-----------+ | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd983_1150363067_data.0.parq | 252.99MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd983_1150363067_data.1.parq | 13.43MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd984_501176748_data.0.parq | 252.84MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd984_501176748_data.1.parq | 63.92MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd985_1199995767_data.0.parq | 183.64MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd986_2086627597_data.0.parq | 240.04MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd987_1048668565_data.0.parq | 211.35MB | | | /user/impala/staging/airlines/4345e5eef217aa1b-c8f16177f35fd988_1432111844_data.0.parq | 151.46MB | | +----------------------------------------------------------------------------------------+----------+-----------+ > DESCRIBE airlines_external; +---------------------+--------+---------------------------------------------------+ | name | type | comment | +---------------------+--------+---------------------------------------------------+ | year | int | Inferred from Parquet file. | | month | int | Inferred from Parquet file. | | day | int | Inferred from Parquet file. | | dayofweek | int | Inferred from Parquet file. | | dep_time | int | Inferred from Parquet file. | | crs_dep_time | int | Inferred from Parquet file. | | arr_time | int | Inferred from Parquet file. | | crs_arr_time | int | Inferred from Parquet file. | | carrier | string | Inferred from Parquet file. | | flight_num | int | Inferred from Parquet file. | | tail_num | int | Inferred from Parquet file. | | actual_elapsed_time | int | Inferred from Parquet file. | | crs_elapsed_time | int | Inferred from Parquet file. | | airtime | int | Inferred from Parquet file. | | arrdelay | int | Inferred from Parquet file. | | depdelay | int | Inferred from Parquet file. | | origin | string | Inferred from Parquet file. | | dest | string | Inferred from Parquet file. | | distance | int | Inferred from Parquet file. | | taxi_in | int | Inferred from Parquet file. | | taxi_out | int | Inferred from Parquet file. | | cancelled | int | Inferred from Parquet file. | | cancellation_code | string | Inferred from Parquet file. | | diverted | int | Inferred from Parquet file. | | carrier_delay | int | Inferred from Parquet file. | | weather_delay | int | Inferred from Parquet file. | | nas_delay | int | Inferred from Parquet file. | | security_delay | int | Inferred from Parquet file. | | late_aircraft_delay | int | Inferred from Parquet file. | +---------------------+--------+---------------------------------------------------+ > DESCRIBE FORMATTED airlines_external; +------------------------------+------------------------------- | name | type +------------------------------+------------------------------- ... | # Detailed Table Information | NULL | Database: | airlines_data | Owner: | impala ... | Location: | /user/impala/staging/airlines | Table Type: | EXTERNAL_TABLE ... | # Storage Information | NULL | SerDe Library: | org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe | InputFormat: | org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputForma | OutputFormat: | org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat ...
Sub Query를 위한 With 사용하기
> SELECT COUNT(*) AS 'rows', COUNT(tail_num) AS 'non-null tail numbers' FROM airlines_external; +-----------+-----------------------+ | rows | non-null tail numbers | +-----------+-----------------------+ | 123534969 | 412968 | +-----------+-----------------------+ > WITH t1 AS (SELECT COUNT(*) AS 'rows', COUNT(tail_num) AS 'nonnull' FROM airlines_external) SELECT `rows`, `nonnull`, `rows` - `nonnull` AS 'nulls', (`nonnull` / `rows`) * 100 AS 'percentage non-null' FROM t1; +-----------+---------+-----------+---------------------+ | rows | nonnull | nulls | percentage non-null | +-----------+---------+-----------+---------------------+ | 123534969 | 412968 | 123122001 | 0.3342923897119365 | +-----------+---------+-----------+---------------------+
테이블의 CREATE 문 확인하기
> SHOW CREATE TABLE airlines_external;
"CREATE EXTERNAL TABLE airlines_data.airlines_external (
year INT COMMENT 'inferred from: optional int32 year',
month INT COMMENT 'inferred from: optional int32 month',
day INT COMMENT 'inferred from: optional int32 day',
dayofweek INT COMMENT 'inferred from: optional int32 dayofweek',
dep_time INT COMMENT 'inferred from: optional int32 dep_time',
crs_dep_time INT COMMENT 'inferred from: optional int32 crs_dep_time',
arr_time INT COMMENT 'inferred from: optional int32 arr_time',
crs_arr_time INT COMMENT 'inferred from: optional int32 crs_arr_time',
carrier STRING COMMENT 'inferred from: optional binary carrier',
flight_num INT COMMENT 'inferred from: optional int32 flight_num',
tail_num INT COMMENT 'inferred from: optional int32 tail_num',
actual_elapsed_time INT COMMENT 'inferred from: optional int32 actual_elapsed_time',
crs_elapsed_time INT COMMENT 'inferred from: optional int32 crs_elapsed_time',
airtime INT COMMENT 'inferred from: optional int32 airtime',
arrdelay INT COMMENT 'inferred from: optional int32 arrdelay',
depdelay INT COMMENT 'inferred from: optional int32 depdelay',
origin STRING COMMENT 'inferred from: optional binary origin',
dest STRING COMMENT 'inferred from: optional binary dest',
distance INT COMMENT 'inferred from: optional int32 distance',
taxi_in INT COMMENT 'inferred from: optional int32 taxi_in',
taxi_out INT COMMENT 'inferred from: optional int32 taxi_out',
cancelled INT COMMENT 'inferred from: optional int32 cancelled',
cancellation_code STRING COMMENT 'inferred from: optional binary cancellation_code',
diverted INT COMMENT 'inferred from: optional int32 diverted',
carrier_delay INT COMMENT 'inferred from: optional int32 carrier_delay',
weather_delay INT COMMENT 'inferred from: optional int32 weather_delay',
nas_delay INT COMMENT 'inferred from: optional int32 nas_delay',
security_delay INT COMMENT 'inferred from: optional int32 security_delay',
late_aircraft_delay INT COMMENT 'inferred from: optional int32 late_aircraft_delay'
)
STORED AS PARQUET
LOCATION 'hdfs://a1730.example.com:8020/user/impala/staging/airlines'
TBLPROPERTIES ('numFiles'='0', 'COLUMN_STATS_ACCURATE'='false',
'transient_lastDdlTime'='1439425228', 'numRows'='-1', 'totalSize'='0',
'rawDataSize'='-1')"
INSERT INTO & SELECT 하기
특정 테이블의 데이터를 로딩하여 다시 INSERT를 수행할때 다음의 쿼리를 실행할 수 있습니다.
> INSERT INTO airlines_data.airlines
PARTITION (year)
SELECT
month,
day,
dayofweek,
dep_time,
crs_dep_time,
arr_time,
crs_arr_time,
carrier,
flight_num,
actual_elapsed_time,
crs_elapsed_time,
airtime,
arrdelay,
depdelay,
origin,
dest,
distance,
taxi_in,
taxi_out,
cancelled,
cancellation_code,
diverted,
carrier_delay,
weather_delay,
nas_delay,
security_delay,
late_aircraft_delay,
year
FROM airlines_data.airlines_external;
COMPUTE STATS
> COMPUTE INCREMENTAL STATS airlines; +-------------------------------------------+ | summary | +-------------------------------------------+ | Updated 22 partition(s) and 27 column(s). | +-------------------------------------------+ > SHOW TABLE STATS airlines; +-------+-----------+--------+----------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------------------------+ | year | #Rows | #Files | Size | Bytes Cached | Cache Replication | Format | Incremental stats | Location | +-------+-----------+--------+----------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------------------------+ | 1987 | 1311826 | 1 | 11.75MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1987 | | 1988 | 5202096 | 1 | 44.04MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1988 | | 1989 | 5041200 | 1 | 46.07MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1989 | | 1990 | 5270893 | 1 | 46.25MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1990 | | 1991 | 5076925 | 1 | 46.77MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1991 | | 1992 | 5092157 | 1 | 48.21MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1992 | | 1993 | 5070501 | 1 | 47.46MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1993 | | 1994 | 5180048 | 1 | 47.47MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1994 | | 1995 | 5327435 | 1 | 62.40MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1995 | | 1996 | 5351983 | 1 | 62.93MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1996 | | 1997 | 5411843 | 1 | 65.05MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1997 | | 1998 | 5384721 | 1 | 62.21MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1998 | | 1999 | 5527884 | 1 | 65.10MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=1999 | | 2000 | 5683047 | 1 | 67.68MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2000 | | 2001 | 5967780 | 1 | 74.03MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2001 | | 2002 | 5271359 | 1 | 74.00MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2002 | | 2003 | 6488540 | 1 | 99.35MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2003 | | 2004 | 7129270 | 1 | 123.29MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2004 | | 2005 | 7140596 | 1 | 120.72MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2005 | | 2006 | 7141922 | 1 | 121.88MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2006 | | 2007 | 7453215 | 1 | 130.87MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2007 | | 2008 | 7009728 | 1 | 123.14MB | NOT CACHED | NOT CACHED | PARQUET | true | hdfs://myhost.com:8020/user/hive/warehouse/airline_data.db/airlines/year=2008 | | Total | 123534969 | 22 | 1.55GB | 0B | | | | | +-------+-----------+--------+----------+--------------+-------------------+---------+-------------------+----------------------------------------------------------------------------------------------------------+