The following guide will show you how to 'Create a database, table and partition' with AWS

Pre-requisites:

1) Bucket created in Amazon S3.

2) Data pushed inside the bucket.

Step1: Create Database

Create Athena Database using the following code:

CREATE DATABASE <your-dbname>

Step 2: Create a Table

Create a table using the following code, replace <your-s3-bucket-names> with your bucket name (in yellow highlights).

CREATE EXTERNAL TABLE <your-dbname>.<table-name> (
   `device_id` string,
    `id_type` string,
    `latitude` float,
    `longitude` float,
    `horizontal_accuracy` float,
    `timestamp` bigint,
    `ip_address` string,
    `device_os` string,
    `os_version` string,
    `user_agent` string,
    `country_code` string,
    `source_id` string,
    `publisherid` string,
    `app_id` string,
    `location_context` string,
    `geohash` string)
 PARTITIONED BY (
   `year` int,
    `month` int,
    `day` int)
 ROW FORMAT DELIMITED
    FIELDS TERMINATED BY ','
  STORED AS INPUTFORMAT
    'org.apache.hadoop.mapred.TextInputFormat'
  OUTPUTFORMAT
    'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
 LOCATION   's3://<your-s3-bucket-name>/'
 TBLPROPERTIES (
   'has_encrypted_data'='false',
    'parquet.compression'='GZIP')

Step 3: Add Partitions

Partition table using the following codes:

ALTER TABLE <your-dbname>.<table-name> ADD IF NOT EXISTS PARTITION
 (year=2019, month=12, day=1) location 's3://<your-s3-bucketname>/2019/12/01'; 

Note: The query has to be run daily. The above example is for 1 December 2019.

We hope that the above technical education piece helps you to create a database, table and partitions, if you need further information, contact us at support@quadrant.io