Format: application/octet-stream
1 {
2 "cells": [
3 {
4 "cell_type": "code",
5 "execution_count": 1,
6 "id": "dd1f295d-af02-4d44-b6c1-b5195e1dd8b3",
7 "metadata": {},
8 "outputs": [],
9 "source": [
10 "import pandas as pd\n",
11 "from sqlalchemy import create_engine\n",
12 "from pyathena import connect\n",
13 "import boto3\n",
14 "import json"
15 ]
16 },
17 {
18 "cell_type": "code",
19 "execution_count": 2,
20 "id": "cb647424-a61f-4aa6-b03a-219518816d2d",
21 "metadata": {},
22 "outputs": [],
23 "source": [
24 "S3_STAGING_DIR = \"s3://aws-athena-query-results-209656230012-us-east-1/\" # Replace with your Athena staging directory\n",
25 "MATCH_LIB_SECRET =\"arn:aws:secretsmanager:us-east-1:209656230012:secret:matchlib_prod_secret-Y593GR\"\n",
26 "AWS_REGION_NAME = \"us-east-1\""
27 ]
28 },
29 {
30 "cell_type": "code",
31 "execution_count": 3,
32 "id": "3ecd4d37-375f-4cc2-9cac-e30225c073a7",
33 "metadata": {},
34 "outputs": [],
35 "source": [
36 "def get_database_secrets(secret):\n",
37 " session = boto3.session.Session()\n",
38 " client = session.client(service_name='secretsmanager', region_name=AWS_REGION_NAME)\n",
39 " get_secret_value_response = client.get_secret_value(SecretId=secret)\n",
40 "\n",
41 " if 'SecretString' in get_secret_value_response:\n",
42 " secrets = json.loads(get_secret_value_response['SecretString'])\n",
43 " else:\n",
44 " secrets = json.loads(base64.b64decode(get_secret_value_response['SecretBinary']).decode('utf-8'))\n",
45 "\n",
46 " return secrets\n",
47 "\n",
48 "def load_data_from_athena(query):\n",
49 " connection = connect(s3_staging_dir=S3_STAGING_DIR, region_name=AWS_REGION_NAME)\n",
50 " df = pd.read_sql(query, connection)\n",
51 " return df"
52 ]
53 },
54 {
55 "cell_type": "code",
56 "execution_count": 4,
57 "id": "e1a1feea-e7e7-4e51-ab38-69486d62d80e",
58 "metadata": {},
59 "outputs": [],
60 "source": [
61 "creds_ml = get_database_secrets(MATCH_LIB_SECRET)"
62 ]
63 },
64 {
65 "cell_type": "code",
66 "execution_count": 5,
67 "id": "a9c92d33-0ce5-4319-a86f-f6de8da494e9",
68 "metadata": {},
69 "outputs": [],
70 "source": [
71 "creds_ml = get_database_secrets(MATCH_LIB_SECRET)\n",
72 "engine_ml = create_engine(f\"postgresql://postgres:{creds_ml['password']}@{creds_ml['host']}/matchlibrary\")"
73 ]
74 },
75 {
76 "cell_type": "code",
77 "execution_count": 6,
78 "id": "96d40228-0348-477e-b29a-df857fe0e019",
79 "metadata": {},
80 "outputs": [],
81 "source": [
82 "query = \"\"\"\n",
83 "select distinct sku, upc,category, subcategory, sub_subcategory, brand , product_title, product_url\n",
84 "from catalog_chewy_chewy_chewy_20231107\n",
85 "where (is_active = true or is_discontinued= false)\n",
86 "and not sku in \n",
87 "(select distinct base_sku from \n",
88 "matches_chewy_chewy_chewy\n",
89 "where active = true \n",
90 "and deleted_date is null )\n",
91 "\"\"\""
92 ]
93 },
94 {
95 "cell_type": "code",
96 "execution_count": 9,
97 "id": "04416695-9e23-4cec-92a2-69d80bd18236",
98 "metadata": {},
99 "outputs": [],
100 "source": [
101 "query =\"\"\"\n",
102 "select distinct product_part_number sku, product_gtin upc ,performanceclassification priority, \n",
103 " product_merch_classification1 category, product_merch_classification2 subcategory, product_merch_classification3 sub_subcategory, \n",
104 " product_purchase_brand brand,concat_ws(' ', product_weight, product_weight_uom) size, \n",
105 " product_name product_title, product_url\n",
106 "from customeringestion.pricingdata_prod_customer_chewy \n",
107 "where product_type = 'Item'\n",
108 "and (product_discontinued_flag = 'false' or product_published_flag = 'true')\n",
109 "and year ='2023' and month ='12' and day = '10'\n",
110 "and not product_part_number in (\n",
111 "select distinct base_sku from match_recall.\"0if_recall_samples\"\n",
112 "union \n",
113 "select distinct sku from match_recall.recall_samples where tenant ='chewy'\n",
114 " )\n",
115 "and not product_part_number in (select distinct base_sku from match_library.match_library_snapshot\n",
116 "where load_date = '2023-12-10' and company_code_base_source_store like '%chewy'\n",
117 "and active = true and deleted_date is null and match ='exact'\n",
118 ")\n",
119 "and not product_part_number in ( select distinct split_part(uuid_a,'<>',2) base_sku \n",
120 "from ml_internal_uat.type_directed_pairs \n",
121 "where uuid_a like '%chewy'\n",
122 "and year ='2023' and month in ('12','11') \n",
123 "and domain ='pets'\n",
124 "union \n",
125 "select distinct split_part(uuid_b,'<>',2) base_sku \n",
126 "from ml_internal_uat.type_directed_pairs \n",
127 "where uuid_b like '%chewy'\n",
128 "and year ='2023' and month in ('12','11') \n",
129 "and domain ='pets' )\n",
130 "and product_merch_classification1 != 'Virtual Bundle'\n",
131 "and not upper(trim(product_purchase_brand)) in ('AMERICAN JOURNEY',\n",
132 "'FRISCO',\n",
133 "'GOODY BOX',\n",
134 "'DR. LYON',\n",
135 "'TYLEE''S',\n",
136 "'ALL KIND',\n",
137 "'BONES & CHEWS',\n",
138 "'USA BONES & CHEWS',\n",
139 "'TRUE ACRE FOODS',\n",
140 "'ONGUARD',\n",
141 "'TINY TIGER') \n",
142 "\"\"\""
143 ]
144 },
145 {
146 "cell_type": "code",
147 "execution_count": 11,
148 "id": "caa2ddf0-e053-410c-960d-08e6e3619b6d",
149 "metadata": {},
150 "outputs": [
151 {
152 "name": "stderr",
153 "output_type": "stream",
154 "text": [
155 "<ipython-input-3-16db1873c241>:15: UserWarning: pandas only supports SQLAlchemy connectable (engine/connection) or database string URI or sqlite3 DBAPI2 connection. Other DBAPI2 objects are not tested. Please consider using SQLAlchemy.\n",
156 " df = pd.read_sql(query, connection)\n"
157 ]
158 }
159 ],
160 "source": [
161 "base_df1 = load_data_from_athena(query)"
162 ]
163 },
164 {
165 "cell_type": "code",
166 "execution_count": 12,
167 "id": "0f560bb7-d7e6-4306-8e35-5e7d6bf44e7a",
168 "metadata": {},
169 "outputs": [
170 {
171 "data": {
172 "text/plain": [
173 "(21377, 10)"
174 ]
175 },
176 "execution_count": 12,
177 "metadata": {},
178 "output_type": "execute_result"
179 }
180 ],
181 "source": [
182 "base_df1.shape"
183 ]
184 },
185 {
186 "cell_type": "code",
187 "execution_count": 68,
188 "id": "ef6a63cc-ec5a-4ed9-9256-fb5885271856",
189 "metadata": {},
190 "outputs": [
191 {
192 "data": {
193 "text/plain": [
194 "(25923, 10)"
195 ]
196 },
197 "execution_count": 68,
198 "metadata": {},
199 "output_type": "execute_result"
200 }
201 ],
202 "source": [
203 "base_df1.shape"
204 ]
205 },
206 {
207 "cell_type": "code",
208 "execution_count": 15,
209 "id": "d8775f73-8c81-48de-b257-82456fc4c873",
210 "metadata": {},
211 "outputs": [
212 {
213 "data": {
214 "text/plain": [
215 "category\n",
216 " 1723\n",
217 "Consumables 1709\n",
218 "Gift Cards 732\n",
219 "Hard Goods 6567\n",
220 "Healthcare 7257\n",
221 "Premium 206\n",
222 "Specialty 3183\n",
223 "Name: sku, dtype: int64"
224 ]
225 },
226 "execution_count": 15,
227 "metadata": {},
228 "output_type": "execute_result"
229 }
230 ],
231 "source": [
232 "base_df1.groupby('category').sku.nunique()"
233 ]
234 },
235 {
236 "cell_type": "code",
237 "execution_count": null,
238 "id": "3331921a-d0c1-4de8-8aa5-9f11ca534d9c",
239 "metadata": {},
240 "outputs": [],
241 "source": [
242 "161\n",
243 "160\n",
244 "68\n",
245 "614\n",
246 "679\n",
247 "19\n",
248 "298\n"
249 ]
250 },
251 {
252 "cell_type": "code",
253 "execution_count": 18,
254 "id": "792a7abc-82c2-4115-aab7-7839476badf7",
255 "metadata": {},
256 "outputs": [],
257 "source": [
258 "ratio = {\n",
259 " '':161,\n",
260 " 'Consumables':\t160,\n",
261 "'Gift Cards':\t68,\n",
262 "'Hard Goods':614,\n",
263 "'Healthcare':679,\n",
264 "'Premium':20,\n",
265 "'Specialty':298\n",
266 "}"
267 ]
268 },
269 {
270 "cell_type": "code",
271 "execution_count": 19,
272 "id": "1c2ebce9-c087-4ad3-8a8a-9d05b362c0ce",
273 "metadata": {},
274 "outputs": [
275 {
276 "data": {
277 "text/plain": [
278 "160"
279 ]
280 },
281 "execution_count": 19,
282 "metadata": {},
283 "output_type": "execute_result"
284 }
285 ],
286 "source": [
287 "ratio['Consumables']"
288 ]
289 },
290 {
291 "cell_type": "code",
292 "execution_count": 20,
293 "id": "82392b65-d606-49c7-9df8-79974c847600",
294 "metadata": {},
295 "outputs": [],
296 "source": [
297 "df = base_df1[base_df1=='xyz'].dropna()"
298 ]
299 },
300 {
301 "cell_type": "code",
302 "execution_count": 21,
303 "id": "daf622da-6a57-421e-92ea-7509ad753259",
304 "metadata": {},
305 "outputs": [
306 {
307 "data": {
308 "text/html": [
309 "<div>\n",
310 "<style scoped>\n",
311 " .dataframe tbody tr th:only-of-type {\n",
312 " vertical-align: middle;\n",
313 " }\n",
314 "\n",
315 " .dataframe tbody tr th {\n",
316 " vertical-align: top;\n",
317 " }\n",
318 "\n",
319 " .dataframe thead th {\n",
320 " text-align: right;\n",
321 " }\n",
322 "</style>\n",
323 "<table border=\"1\" class=\"dataframe\">\n",
324 " <thead>\n",
325 " <tr style=\"text-align: right;\">\n",
326 " <th></th>\n",
327 " <th>sku</th>\n",
328 " <th>upc</th>\n",
329 " <th>priority</th>\n",
330 " <th>category</th>\n",
331 " <th>subcategory</th>\n",
332 " <th>sub_subcategory</th>\n",
333 " <th>brand</th>\n",
334 " <th>size</th>\n",
335 " <th>product_title</th>\n",
336 " <th>product_url</th>\n",
337 " </tr>\n",
338 " </thead>\n",
339 " <tbody>\n",
340 " </tbody>\n",
341 "</table>\n",
342 "</div>"
343 ],
344 "text/plain": [
345 "Empty DataFrame\n",
346 "Columns: [sku, upc, priority, category, subcategory, sub_subcategory, brand, size, product_title, product_url]\n",
347 "Index: []"
348 ]
349 },
350 "execution_count": 21,
351 "metadata": {},
352 "output_type": "execute_result"
353 }
354 ],
355 "source": [
356 "df"
357 ]
358 },
359 {
360 "cell_type": "code",
361 "execution_count": 22,
362 "id": "9cfade0a-9b3b-4e28-9094-6fdf3aebd62d",
363 "metadata": {},
364 "outputs": [],
365 "source": [
366 "for i in base_df1.category.unique():\n",
367 " n = ratio[i]\n",
368 " d = base_df1[base_df1.category == i].reset_index(drop = True).iloc[:n,:]\n",
369 " df = pd.concat([d,df], ignore_index = True)"
370 ]
371 },
372 {
373 "cell_type": "code",
374 "execution_count": 23,
375 "id": "cb8f9e89-40db-4982-b6cf-2351bfe98419",
376 "metadata": {},
377 "outputs": [
378 {
379 "data": {
380 "text/plain": [
381 "(2000, 10)"
382 ]
383 },
384 "execution_count": 23,
385 "metadata": {},
386 "output_type": "execute_result"
387 }
388 ],
389 "source": [
390 "df.shape"
391 ]
392 },
393 {
394 "cell_type": "code",
395 "execution_count": 24,
396 "id": "5f0978e0-a72f-40b6-b456-637e14edc2bf",
397 "metadata": {},
398 "outputs": [],
399 "source": [
400 "df.to_parquet('s3://match.recall/dev/recall_samples/tenant=chewy/load_date=2023-12-11/recall_sample.parquet', index = False)"
401 ]
402 },
403 {
404 "cell_type": "code",
405 "execution_count": null,
406 "id": "4097b645-2f1a-4cd7-a7e5-10dc99ab77f3",
407 "metadata": {},
408 "outputs": [],
409 "source": []
410 }
411 ],
412 "metadata": {
413 "kernelspec": {
414 "display_name": "Python 3",
415 "language": "python",
416 "name": "python3"
417 },
418 "language_info": {
419 "codemirror_mode": {
420 "name": "ipython",
421 "version": 3
422 },
423 "file_extension": ".py",
424 "mimetype": "text/x-python",
425 "name": "python",
426 "nbconvert_exporter": "python",
427 "pygments_lexer": "ipython3",
428 "version": "3.9.7"
429 }
430 },
431 "nbformat": 4,
432 "nbformat_minor": 5
433 }
434