{"metadata":{"kernelspec":{"language":"python","display_name":"Python 3","name":"python3"},"language_info":{"name":"python","version":"3.10.14","mimetype":"text/x-python","codemirror_mode":{"name":"ipython","version":3},"pygments_lexer":"ipython3","nbconvert_exporter":"python","file_extension":".py"},"kaggle":{"accelerator":"none","dataSources":[{"sourceId":84493,"databundleVersionId":9871156,"sourceType":"competition"},{"sourceId":203781885,"sourceType":"kernelVersion"}],"dockerImageVersionId":30786,"isInternetEnabled":true,"language":"python","sourceType":"notebook","isGpuEnabled":false}},"nbformat_minor":4,"nbformat":4,"cells":[{"cell_type":"code","source":"","metadata":{},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **FOREWORD**\n\nThis is a starter kernel to illustrate the usage of SQL in Polars. <br>\nThis kernel has 3-4 examples of how one could incorporate SQL code snippets into one's Polars FE pipeline to ease his/ her workflow. Many of us are new to Polars and I am sure this will be helpful to onboard effectively <br>\n\nWishing everyone the best!","metadata":{}},{"cell_type":"markdown","source":"# **IMPORTS**\n\nWe will work with the last released version of **Polars==1.1.12** as on 28Oct2024. Wheel files are provided [here](https://www.kaggle.com/code/ravi20076/janestreet2024-imports-v1) for ready installation. <br>\nRelease notes for Polars 1.1.12 and yester versions are provided [here](https://github.com/pola-rs/polars/releases)\nI shall install the library as-is from the downloaded wheel file. <br>\n\nPlease find SQL relevant references from the Polars documentation page [here](https://docs.pola.rs/api/python/stable/reference/sql/python_api.html#introduction) for further reading. ","metadata":{}},{"cell_type":"code","source":"%%time \n\n!pip install polars[gpu]==1.12.0 -q --no-index --find-links=/kaggle/input/janestreet2024-imports-v1/polars1120\n\nexec(\n    open(\"/kaggle/input/janestreet2024-imports-v1/myimports.py\", \"r\"\n        ).read()\n)\n\nprint()","metadata":{"execution":{"iopub.status.busy":"2024-10-28T05:59:04.528689Z","iopub.execute_input":"2024-10-28T05:59:04.529080Z","iopub.status.idle":"2024-10-28T05:59:21.579690Z","shell.execute_reply.started":"2024-10-28T05:59:04.529045Z","shell.execute_reply":"2024-10-28T05:59:21.578547Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **DATA LOADS**","metadata":{}},{"cell_type":"code","source":"%%time \n\ntrain = pl.scan_parquet(f\"/kaggle/input/jane-street-real-time-market-data-forecasting/train.parquet\")","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:00:19.629218Z","iopub.execute_input":"2024-10-28T06:00:19.630646Z","iopub.status.idle":"2024-10-28T06:00:19.637865Z","shell.execute_reply.started":"2024-10-28T06:00:19.630594Z","shell.execute_reply":"2024-10-28T06:00:19.636675Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"# **SQL USAGE FORMS**\n\nThis section highlights several examples of how one could leverage SQL in one's polars dataframe/ lazyframe and ease one's selection process","metadata":{}},{"cell_type":"markdown","source":"## **USING GLOBAL - pl.sql**<br>\n\npl.sql is a very useful polars SQL interface that allows us to use more than 1 polars/ pandas tables and series in a single SQL query. One could use single/ multiple tables and series and structure a SQL multiline query using the syntax below- <br>","metadata":{}},{"cell_type":"code","source":"%%time \n\npl.sql(\n    \"\"\"\n    SELECT responder_6 from train\n    LIMIT 10\n    \"\"\"\n).collect()","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:00:56.682070Z","iopub.execute_input":"2024-10-28T06:00:56.682497Z","iopub.status.idle":"2024-10-28T06:00:56.767929Z","shell.execute_reply.started":"2024-10-28T06:00:56.682454Z","shell.execute_reply":"2024-10-28T06:00:56.766743Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \n\npl.sql(\n    \"\"\"\n    SELECT date_id, time_id, responder_6 + responder_7 + responder_8 AS mysum, \n           COALESCE(feature_21, 0) AS my_feature_21 \n    FROM train\n    LIMIT 100\n    \"\"\"\n).collect()","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:06:27.156246Z","iopub.execute_input":"2024-10-28T06:06:27.156692Z","iopub.status.idle":"2024-10-28T06:06:27.178251Z","shell.execute_reply.started":"2024-10-28T06:06:27.156651Z","shell.execute_reply":"2024-10-28T06:06:27.177151Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## **USING FRAME LEVEL SQL**\n\nThis is an alternative usage of pl.SQL on a given frame (lazy/ eager) with an optional frame registration as well. I like this a lot personally and have used this a lot at work as well! <br>\n\nThe block below highlights how to use this lazily and the one below it is the same query with an eager frame. <br>","metadata":{}},{"cell_type":"code","source":"%%time \n\ntrain.sql(\n    \"\"\"\n    SELECT date_id, time_id, CAST(symbol_id AS string) as str_symbol_id, \n           feature_00, COALESCE(feature_01, -1) + 5 as new_feature_01 \n    FROM self\n    LIMIT 100\n    \"\"\"\n).show_graph()","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:14:26.464872Z","iopub.execute_input":"2024-10-28T06:14:26.465296Z","iopub.status.idle":"2024-10-28T06:14:26.493126Z","shell.execute_reply.started":"2024-10-28T06:14:26.465233Z","shell.execute_reply":"2024-10-28T06:14:26.491934Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \n\ntrain.sql(\n    \"\"\"\n    SELECT date_id, time_id, CAST(symbol_id AS string) as str_symbol_id, \n           feature_00, COALESCE(feature_01, -1) + 5 as new_feature_01 \n    FROM self\n    LIMIT 100\n    \"\"\"\n).collect()","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:15:12.609639Z","iopub.execute_input":"2024-10-28T06:15:12.610068Z","iopub.status.idle":"2024-10-28T06:15:12.627527Z","shell.execute_reply.started":"2024-10-28T06:15:12.610025Z","shell.execute_reply":"2024-10-28T06:15:12.626425Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## **SQL CONTEXT**\n\nThis is another excellent way to use SQL with eager/ lazy frames and acts as a nice context manager with table registration facility as well. <br>\nOne can control lazy execution with the parameter **eager = True/ False** - this is the best feature of this context manager! <br>\nPlease note that you will have to define a mapping between the original frame and the frames within the context manager while registering them, else it will return an error. Note that I have registered frame **train as df_1** for illustration below <br>","metadata":{}},{"cell_type":"code","source":"%%time \n\nwith pl.SQLContext(df_1 = train, eager = False) as myctx:\n    df1 = \\\n    myctx.execute(\n        \"\"\"\n        SELECT date_id, time_id, symbol_id, responder_6, responder_7\n        FROM df_1\n        WHERE date_id == 1000\n        LIMIT 100\n        \"\"\"\n    )\n    \n    df2 = \\\n    myctx.execute(\n        \"\"\"\n        SELECT date_id, time_id, symbol_id, feature_01\n        FROM df_1\n        WHERE date_id == 1000\n        LIMIT 100\n        \"\"\"\n    )\n    \n    display(df1.\n            join(df2, \n                 how = \"left\", on = [\"date_id\", \"time_id\", \"symbol_id\"]\n                ).\n            show_graph(figsize = (20, 20)) \n           )\n    ","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:29:39.131929Z","iopub.execute_input":"2024-10-28T06:29:39.132400Z","iopub.status.idle":"2024-10-28T06:29:39.169015Z","shell.execute_reply.started":"2024-10-28T06:29:39.132352Z","shell.execute_reply":"2024-10-28T06:29:39.167941Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"## **USING SQL AND NATIVE POLARS SYNERGY**\n\nWe can use SQL within native polars syntax, especially while adding new columns to the eager/ lazy frame to simplify our process. This is a great feature to have and a powerful addition in my opinion! <br>\nPolars translates the SQL expression under the hood and integrates it to our benefit. One can use SQL expression in one column and native polars syntax for another, making them fully integrated within the same *with_columns* block!\n\n","metadata":{}},{"cell_type":"code","source":"%%time \n\ntrain.\\\nwith_columns(\n    pl.sql_expr(\"coalesce(feature_00, 0.1) + 10 as new_feature_00\"),\n    pl.sql_expr(\"coalesce(feature_45, 1) - 5 + coalesce(feature_46, 8) as new_feature\"),\n    (pl.col(\"feature_09\") + 10).alias(\"new_feature_09\"),\n).show_graph(figsize = (20, 20))","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:37:15.663602Z","iopub.execute_input":"2024-10-28T06:37:15.664004Z","iopub.status.idle":"2024-10-28T06:37:15.694301Z","shell.execute_reply.started":"2024-10-28T06:37:15.663969Z","shell.execute_reply":"2024-10-28T06:37:15.693184Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"code","source":"%%time \n\ntrain.\\\nwith_columns(\n    pl.sql_expr(\"coalesce(feature_00, 0.1) + 10 as new_feature_00\"),\n    pl.sql_expr(\"coalesce(feature_45, 1) - 5 + coalesce(feature_46, 8) as new_feature\"),\n    (pl.col(\"feature_09\") + 10).alias(\"new_feature_09\"),\n).\\\nhead(10).\\\nselect(pl.col([\"date_id\", \"time_id\", \"new_feature_00\", \"new_feature\", \"new_feature_09\"])).\\\ncollect()","metadata":{"execution":{"iopub.status.busy":"2024-10-28T06:37:35.506987Z","iopub.execute_input":"2024-10-28T06:37:35.507473Z","iopub.status.idle":"2024-10-28T06:37:35.529310Z","shell.execute_reply.started":"2024-10-28T06:37:35.507424Z","shell.execute_reply":"2024-10-28T06:37:35.528207Z"},"trusted":true},"execution_count":null,"outputs":[]},{"cell_type":"markdown","source":"### Are you also going to use polars-SQL in your workflows? Thoughts? Comments?","metadata":{}}]}