hVpUQfp9

1create external schema spectrum_polku_dev from data catalog
2database 'lake-spectrum' 
3iam_role 'arn:aws:iam::531820706523:role/lake-redshift-DEV-RedshiftIamRole-1WJEOPK9KU5MF'
4create external database if not exists;
5
6drop table spectrum_polku_dev.offer_found_parquet;
7create external table spectrum_polku_dev.offer_found_parquet (
8anonymous_id varchar,
9context_app_name varchar,
10context_ip bigint,
11context_user_agent_md5 varchar,
12message_id varchar,
13polku_context_resource_path varchar,
14properties_available_rooms bigint,
15properties_can_pay_later boolean,
16properties_has_free_cancellation boolean,
17properties_hotel_decorator varchar,
18properties_hotel_distance_in_meters bigint,
19properties_hotel_id varchar,
20properties_hotel_index bigint,
21properties_hotel_no_offers_returned boolean,
22properties_hotel_number_of_providers bigint,
23properties_hotel_number_of_rates bigint,
24properties_inclusions varchar,
25properties_index bigint,
26properties_is_best boolean,
27properties_is_bundled_rate boolean,
28properties_is_cheapest_rate boolean,
29properties_is_worst boolean,
30properties_offer_id varchar,
31properties_original_price float,
32properties_provider_code varchar,
33properties_room_name varchar,
34properties_search_check_in varchar,
35properties_search_check_out varchar,
36properties_search_currency varchar,
37properties_search_hotel_id varchar,
38properties_search_locale varchar,
39properties_search_message_id varchar,
40properties_search_place_id bigint,
41properties_search_response_is_complete boolean,
42properties_search_response_number_of_offers bigint,
43properties_search_response_number_of_providers bigint,
44properties_search_rooms varchar,
45properties_search_search_id varchar,
46properties_search_search_type varchar,
47properties_search_session_id varchar,
48properties_search_sorting_page_index bigint,
49properties_search_sorting_page_size bigint,
50properties_search_sorting_sort_field varchar,
51properties_search_sorting_sort_order varchar,
52properties_total_rate float,
53sent_at timestamp,
54"timestamp" timestamp,
55user_id varchar)
56stored as parquet
57location 's3://fh-lake-dev/lucro-spectrum/dev/polku_dev.offer_found/parquet';
58
59drop table spectrum_polku_dev.offer_found_avro;
60create external table spectrum_polku_dev.offer_found_avro (
61anonymous_id varchar,
62context_app_name varchar,
63context_ip bigint,
64context_user_agent_md5 varchar,
65message_id varchar,
66polku_context_resource_path varchar,
67properties_available_rooms bigint,
68properties_can_pay_later boolean,
69properties_has_free_cancellation boolean,
70properties_hotel_decorator varchar,
71properties_hotel_distance_in_meters bigint,
72properties_hotel_id varchar,
73properties_hotel_index bigint,
74properties_hotel_no_offers_returned boolean,
75properties_hotel_number_of_providers bigint,
76properties_hotel_number_of_rates bigint,
77properties_inclusions varchar,
78properties_index bigint,
79properties_is_best boolean,
80properties_is_bundled_rate boolean,
81properties_is_cheapest_rate boolean,
82properties_is_worst boolean,
83properties_offer_id varchar,
84properties_original_price float,
85properties_provider_code varchar,
86properties_room_name varchar,
87properties_search_check_in varchar,
88properties_search_check_out varchar,
89properties_search_currency varchar,
90properties_search_hotel_id varchar,
91properties_search_locale varchar,
92properties_search_message_id varchar,
93properties_search_place_id bigint,
94properties_search_response_is_complete boolean,
95properties_search_response_number_of_offers bigint,
96properties_search_response_number_of_providers bigint,
97properties_search_rooms varchar,
98properties_search_search_id varchar,
99properties_search_search_type varchar,
100properties_search_session_id varchar,
101properties_search_sorting_page_index bigint,
102properties_search_sorting_page_size bigint,
103properties_search_sorting_sort_field varchar,
104properties_search_sorting_sort_order varchar,
105properties_total_rate float,
106sent_at timestamp,
107"timestamp" timestamp,
108user_id varchar)
109ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.avro.AvroSerDe'
110WITH SERDEPROPERTIES ('avro.schema.literal'='{\"namespace\":\"org.findhotel\",
111\"name\":\"polku_dev.offer_found\",\"type\": \"record\",
112\"fields\": [{\"name\":\"anonymous_id\",\"type\":[\"null\",\"string\"]},
113{\"name\":\"context_app_name\",\"type\":[\"null\",\"string\"]},
114{\"name\":\"context_ip\",\"type\":[\"null\",\"long\"]},
115{\"name\":\"context_user_agent_md5\",\"type\":[\"null\",\"string\"]},
116{\"name\":\"message_id\",\"type\":[\"null\",\"string\"]},
117{\"name\":\"polku_context_resource_path\",\"type\":[\"null\",\"string\"]},
118{\"name\":\"properties_available_rooms\",\"type\":[\"null\",\"long\"]},
119{\"name\":\"properties_can_pay_later\",\"type\":[\"null\",\"boolean\"]},
120{\"name\":\"properties_has_free_cancellation\",\"type\":[\"null\",\"boolean\"]},
121{\"name\":\"properties_hotel_decorator\",\"type\":[\"null\",\"string\"]},
122{\"name\":\"properties_hotel_distance_in_meters\",\"type\":[\"null\",\"long\"]},
123{\"name\":\"properties_hotel_id\",\"type\":[\"null\",\"string\"]},
124{\"name\":\"properties_hotel_index\",\"type\":[\"null\",\"long\"]},
125{\"name\":\"properties_hotel_no_offers_returned\",\"type\":[\"null\",\"boolean\"]},
126{\"name\":\"properties_hotel_number_of_providers\",\"type\":[\"null\",\"long\"]},
127{\"name\":\"properties_hotel_number_of_rates\",\"type\":[\"null\",\"long\"]},
128{\"name\":\"properties_inclusions\",\"type\":[\"null\",\"string\"]},
129{\"name\":\"properties_index\",\"type\":[\"null\",\"long\"]},
130{\"name\":\"properties_is_best\",\"type\":[\"null\",\"boolean\"]},
131{\"name\":\"properties_is_bundled_rate\",\"type\":[\"null\",\"boolean\"]},
132{\"name\":\"properties_is_cheapest_rate\",\"type\":[\"null\",\"boolean\"]},
133{\"name\":\"properties_is_worst\",\"type\":[\"null\",\"boolean\"]},
134{\"name\":\"properties_offer_id\",\"type\":[\"null\",\"string\"]},
135{\"name\":\"properties_original_price\",\"type\":[\"null\",\"float\"]},
136{\"name\":\"properties_provider_code\",\"type\":[\"null\",\"string\"]},
137{\"name\":\"properties_room_name\",\"type\":[\"null\",\"string\"]},
138{\"name\":\"properties_search_check_in\",\"type\":[\"null\",\"string\"]},
139{\"name\":\"properties_search_check_out\",\"type\":[\"null\",\"string\"]},
140{\"name\":\"properties_search_currency\",\"type\":[\"null\",\"string\"]},
141{\"name\":\"properties_search_hotel_id\",\"type\":[\"null\",\"string\"]},
142{\"name\":\"properties_search_locale\",\"type\":[\"null\",\"string\"]},
143{\"name\":\"properties_search_message_id\",\"type\":[\"null\",\"string\"]},
144{\"name\":\"properties_search_place_id\",\"type\":[\"null\",\"long\"]},
145{\"name\":\"properties_search_response_is_complete\",\"type\":[\"null\",\"boolean\"]},
146{\"name\":\"properties_search_response_number_of_offers\",\"type\":[\"null\",\"long\"]},
147{\"name\":\"properties_search_response_number_of_providers\",\"type\":[\"null\",\"long\"]},
148{\"name\":\"properties_search_rooms\",\"type\":[\"null\",\"string\"]},
149{\"name\":\"properties_search_search_id\",\"type\":[\"null\",\"string\"]},
150{\"name\":\"properties_search_search_type\",\"type\":[\"null\",\"string\"]},
151{\"name\":\"properties_search_session_id\",\"type\":[\"null\",\"string\"]},
152{\"name\":\"properties_search_sorting_page_index\",\"type\":[\"null\",\"long\"]},
153{\"name\":\"properties_search_sorting_page_size\",\"type\":[\"null\",\"long\"]},
154{\"name\":\"properties_search_sorting_sort_field\",\"type\":[\"null\",\"string\"]},
155{\"name\":\"properties_search_sorting_sort_order\",\"type\":[\"null\",\"string\"]},
156{\"name\":\"properties_total_rate\",\"type\":[\"null\",\"float\"]},
157{\"name\":\"sent_at\",\"type\":{\"type\":\"long\",\"logicalType\":\"timestamp-millis\"}},
158{\"name\":\"timestamp\",\"type\":{\"type\":\"long\",\"logicalType\":\"timestamp-millis\"}}]}')
159stored as avro
160location 's3://fh-lake-dev/lucro-spectrum/dev/polku_dev.offer_found/avro';