Rich-text Reply
Highlighted

Import raw event data to own s3 bucket and filter with Athena

moritz a week ago
Accepted Solution

Import raw event data to own s3 bucket and filter with Athena

Hi,

we want to import the raw optimizely x data into our own s3 bucket and do queries on AWS with Athena. Can you provide a database example for that case? Something seems a bit strange with the imported data with the following configuration and i dont know if there are corrupted files or a invalid definition.

`timestamp` string,
`project_id` string,
`experiment_id` string,
`variation_id` string,
`layer_holdback` string,
`audience_names` string,
`end_user_id` string,
`uuid` string,
`session_id` string,
`snippet_revision` string,
`user_ip` string,
`user_agent` string,
`user_engine` string,
`user_engine_version` string,
`referer` string,
`global_holdback` string,
`event_type` string,
`event_name` string,
`user_features` string,
`active_views` string,
`event_features` string,
`event_metrics` string

I know optimizely nor the comunity cant do the aws support but perhaps here is someone with the same use case.

 

regards

moritz

--
Developer for cro
Level 2

moritz a week ago
 

Re: Import raw event data to own s3 bucket and filter with Athena

Solved it:

CREATE EXTERNAL TABLE IF NOT EXISTS YOURDB.tablename (
`timestamp ` string,
`project_id` string,
`campaign_id` string,
`experiment_id` string,
`variation_id` string,
`layer_holdback` string,
`audience_names` string,
`end_user_id` string,
`uuid` string,
`session_id` string,
`snippet_revision` string,
`user_ip` string,
`user_agent` string,
`user_engine` string,
`user_engine_version` string,
`referer` string,
`global_holdback` string,
`event_type` string,
`event_name` string,
`user_features` string,
`active_views` string,
`event_features` string,
`event_metrics` string
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
'serialization.format' = ' ',
'field.delim' = ' '
) LOCATION 's3://yourbucket/'
TBLPROPERTIES ('has_encrypted_data'='false')

--
Developer for cro
Level 2