QUADRANT DATA OVERVIEW

Product Overview

Data Dictionary

Global Data Counts

About Mobile Location Data

 

RESOURCES

Cross Account Bucket Access

A. AWS S3

Getting Started with Quadrant Mobile Location Data

A. Create a Database, Table and Partition

B. How To Run Basic Location Data Queries

i. Scale and Trend

ii. Depth

iii. Accuracy

C. How Geohash Works 

All You Need To Know About Data Evaluation

A. Best Practices

B. SDK vs Bidstream Data

Location Data Algorithms and Queries

A. Geo-fencing Query

B. Footfall Query

C. Nearest POI Model

D. Location Algorithms

 

SDK INTEGRATIONS

Android Integration

iOS Integration

Integrate with Unity3D for Android

Integrate with Unity3D for iOS

 

ASIA PACIFIC DATA ALLIANCE

About The Alliance

The Data

Use Cases & Data Science Algorithms

Access APAC Data Alliance Data with AWS S3

 
MEDIA LIBRARY

 

FREQUENTLY ASKED QUESTIONS

 

 

 

 

 

 

CREATE A DATABASE, TABLE AND PARTITION


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 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