Create Table in Athena

JSON

CREATE EXTERNAL TABLE IF NOT EXISTS default.sensor (
    sensorId int,
    temperature int
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://sensor-datas/data/';

Parquet

CREATE EXTERNAL TABLE IF NOT EXISTS default.sensor (
    sensorId int,
    temperature int
)
STORED AS parquet
LOCATION 's3://sensor-datas/data/'
TBLPROPERTIES ("parquet.compress" = "snappy");

CSV

CREATE EXTERNAL TABLE IF NOT EXISTS default.sensor (
    sensorId int,
    temperature int
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
LOCATION 's3://sensor-datas/data/'

Tip

If you want to ignore csv's header, use this option after LOCATION syntax.

TBLPROPERTIES ("skip.header.line.count" = "1");

Partitioning

CREATE EXTERNAL TABLE IF NOT EXISTS default.sensor (
    sensorId int,
    temperature int
)
PARTITIONED BY (`year` int, `month` int, `day` int, `hour` int)
STORED AS parquet
LOCATION 's3://sensor-datas/data/'
TBLPROPERTIES ("parquet.compress" = "snappy");

Compression

CREATE EXTERNAL TABLE IF NOT EXISTS default.sensor (
    sensorId int,
    temperature int
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://sensor-datas/data/'
TBLPROPERTIES ("write.compression" = "gzip ");