PySpark2 filtrowanie.ipynb

{"cells":[{"cell_type":"markdown","source":["## PySpark\n","### Filtrowanie"],"metadata":{"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"f4c85764-f47c-4c84-8780-a987f6740e22"},{"cell_type":"code","source":["# utworzenie data frame\n","df = spark.sql(\"SELECT ProductID, Name, ProductNumber, MakeFlag, Color, ListPrice FROM AdventureWorks.Production_Product\")"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"eef46822-6e60-4b4b-9700-5983eb0c6618","statement_id":4,"statement_ids":[4],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T12:22:05.9840306Z","session_start_time":null,"execution_start_time":"2024-05-14T12:22:07.9693238Z","execution_finish_time":"2024-05-14T12:22:24.2741915Z","parent_msg_id":"618e2332-6f60-47c0-bfe2-8cc11e409948"},"text/plain":"StatementMeta(, eef46822-6e60-4b4b-9700-5983eb0c6618, 4, Finished, Available)"},"metadata":{}}],"execution_count":2,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"01207435-d732-4df8-a0d6-5a3905501a4e"},{"cell_type":"code","source":["# filter\n","\n","df.filter(df.Color=='Blue').show(3)\n","df.filter(df.Color!='Blue').show(3)"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":37,"statement_ids":[37],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:46:53.5188686Z","session_start_time":null,"execution_start_time":"2024-05-14T09:46:54.03032Z","execution_finish_time":"2024-05-14T09:46:55.6696113Z","parent_msg_id":"ba87dd04-e7ec-4d32-b045-1d0343b33d8f"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 37, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+-------------+--------+-----+----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|Size|ListPrice|\n+---------+--------------------+-------------+--------+-----+----+---------+\n|      895|LL Touring Frame ...|   FR-T67U-50|    true| Blue|  50| 333.4200|\n|      896|LL Touring Frame ...|   FR-T67U-54|    true| Blue|  54| 333.4200|\n|      897|LL Touring Frame ...|   FR-T67U-58|    true| Blue|  58| 333.4200|\n+---------+--------------------+-------------+--------+-----+----+---------+\nonly showing top 3 rows\n\n+---------+----------------+-------------+--------+-----+----+---------+\n|ProductID|            Name|ProductNumber|MakeFlag|Color|Size|ListPrice|\n+---------+----------------+-------------+--------+-----+----+---------+\n|      759|Road-650 Red, 58|   BK-R50R-58|    true|  Red|  58| 782.9900|\n|      760|Road-650 Red, 60|   BK-R50R-60|    true|  Red|  60| 782.9900|\n|      761|Road-650 Red, 62|   BK-R50R-62|    true|  Red|  62| 782.9900|\n+---------+----------------+-------------+--------+-----+----+---------+\nonly showing top 3 rows\n\n"]}],"execution_count":35,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"309c7ed9-583e-49ad-8064-ae331b1eaf71"},{"cell_type":"code","source":["# filter\n","# bardziej SQL-owo\n","\n","df.filter(\"Color=='Blue'\").show(3)\n","df.filter(\"Color!='Blue'\").show(3)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":25,"statement_ids":[25],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:41:43.8425891Z","session_start_time":null,"execution_start_time":"2024-05-14T09:41:44.2999128Z","execution_finish_time":"2024-05-14T09:41:45.8752259Z","parent_msg_id":"ba4f3d0b-f6b6-49ac-a18d-6cb77e97add1"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 25, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+-------------+--------+-----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+--------------------+-------------+--------+-----+---------+\n|      895|LL Touring Frame ...|   FR-T67U-50|    true| Blue| 333.4200|\n|      896|LL Touring Frame ...|   FR-T67U-54|    true| Blue| 333.4200|\n|      897|LL Touring Frame ...|   FR-T67U-58|    true| Blue| 333.4200|\n+---------+--------------------+-------------+--------+-----+---------+\nonly showing top 3 rows\n\n+---------+----------------+-------------+--------+-----+---------+\n|ProductID|            Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+----------------+-------------+--------+-----+---------+\n|      759|Road-650 Red, 58|   BK-R50R-58|    true|  Red| 782.9900|\n|      760|Road-650 Red, 60|   BK-R50R-60|    true|  Red| 782.9900|\n|      761|Road-650 Red, 62|   BK-R50R-62|    true|  Red| 782.9900|\n+---------+----------------+-------------+--------+-----+---------+\nonly showing top 3 rows\n\n"]}],"execution_count":23,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"a2b1e225-6a71-498e-8833-c02bc1f36c31"},{"cell_type":"code","source":["# distinct - usuwanie dupikatów\n","\n","df.select(df.Color).distinct().show()"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":38,"statement_ids":[38],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:46:59.2763363Z","session_start_time":null,"execution_start_time":"2024-05-14T09:46:59.7453612Z","execution_finish_time":"2024-05-14T09:47:00.5863998Z","parent_msg_id":"3d49a544-e5a7-47d3-bdee-1a4ba432e55e"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 38, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+------------+\n|       Color|\n+------------+\n|        null|\n|       Multi|\n|      Silver|\n|        Blue|\n|       White|\n|       Black|\n|      Yellow|\n|         Red|\n|        Grey|\n|Silver/Black|\n+------------+\n\n"]}],"execution_count":36,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"4aa43bd1-37db-40f7-8f02-c80a630a579f"},{"cell_type":"code","source":["# filter\n","\n","df.filter(df.MakeFlag==True).show(3)\n","df.filter(df.MakeFlag!=True).show(3)"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":9,"statement_ids":[9],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:13:41.6342808Z","session_start_time":null,"execution_start_time":"2024-05-14T09:13:43.4890818Z","execution_finish_time":"2024-05-14T09:13:45.292132Z","parent_msg_id":"f9244c8e-c63a-4c6c-b592-98b491cef47b"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 9, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+----------------+-------------+--------+-----+---------+\n|ProductID|            Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+----------------+-------------+--------+-----+---------+\n|      759|Road-650 Red, 58|   BK-R50R-58|    true|  Red| 782.9900|\n|      760|Road-650 Red, 60|   BK-R50R-60|    true|  Red| 782.9900|\n|      761|Road-650 Red, 62|   BK-R50R-62|    true|  Red| 782.9900|\n+---------+----------------+-------------+--------+-----+---------+\nonly showing top 3 rows\n\n+---------+-----------------+-------------+--------+------------+---------+\n|ProductID|             Name|ProductNumber|MakeFlag|       Color|ListPrice|\n+---------+-----------------+-------------+--------+------------+---------+\n|      907|      Rear Brakes|      RB-9231|   false|      Silver| 106.5000|\n|      935|LL Mountain Pedal|      PD-M282|   false|Silver/Black|  40.4900|\n|      936|ML Mountain Pedal|      PD-M340|   false|Silver/Black|  62.0900|\n+---------+-----------------+-------------+--------+------------+---------+\nonly showing top 3 rows\n\n"]}],"execution_count":7,"metadata":{"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"f8e1e9a0-7471-4e10-8be8-c290500902f8"},{"cell_type":"code","source":["#filter, where, between\n","\n","df.filter(df.ListPrice.between(100,500)).show(3)\n","df.where(df.ListPrice.between(100,500)).show(3)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":10,"statement_ids":[10],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:13:41.7251053Z","session_start_time":null,"execution_start_time":"2024-05-14T09:13:45.873999Z","execution_finish_time":"2024-05-14T09:13:47.4661309Z","parent_msg_id":"b4359ae5-0119-4d6c-8f0b-ae48d6578e90"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 10, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+-------------+--------+-----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+--------------------+-------------+--------+-----+---------+\n|      722|LL Road Frame - B...|   FR-R38B-58|    true|Black| 337.2200|\n|      723|LL Road Frame - B...|   FR-R38B-60|    true|Black| 337.2200|\n|      724|LL Road Frame - B...|   FR-R38B-62|    true|Black| 337.2200|\n+---------+--------------------+-------------+--------+-----+---------+\nonly showing top 3 rows\n\n+---------+--------------------+-------------+--------+-----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+--------------------+-------------+--------+-----+---------+\n|      722|LL Road Frame - B...|   FR-R38B-58|    true|Black| 337.2200|\n|      723|LL Road Frame - B...|   FR-R38B-60|    true|Black| 337.2200|\n|      724|LL Road Frame - B...|   FR-R38B-62|    true|Black| 337.2200|\n+---------+--------------------+-------------+--------+-----+---------+\nonly showing top 3 rows\n\n"]}],"execution_count":8,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"a8c97035-7d29-492e-bbfe-b15ac98f9292"},{"cell_type":"code","source":["# filter, contains\n","\n","df.filter(df.Name.contains(\"Ball\")).show()"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":11,"statement_ids":[11],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:13:41.8336032Z","session_start_time":null,"execution_start_time":"2024-05-14T09:13:47.9933554Z","execution_finish_time":"2024-05-14T09:13:48.9385473Z","parent_msg_id":"c5085e76-ec04-456b-a703-66d024780714"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 11, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+-------------+--------+-----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+--------------------+-------------+--------+-----+---------+\n|        3|     BB Ball Bearing|      BE-2349|    true| null|   0.0000|\n|        4|Headset Ball Bear...|      BE-2908|   false| null|   0.0000|\n|        2|        Bearing Ball|      BA-8327|   false| null|   0.0000|\n+---------+--------------------+-------------+--------+-----+---------+\n\n"]}],"execution_count":9,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"0c9f2b2a-6896-438b-bea0-56f5507157af"},{"cell_type":"code","source":["# filter, startswith, endwith\n","\n","df.filter(df.Name.startswith(\"B\")).show()\n","\n","df.filter(df.Name.endswith(\"karm\")).show()\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":12,"statement_ids":[12],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:13:42.0051167Z","session_start_time":null,"execution_start_time":"2024-05-14T09:13:49.4717281Z","execution_finish_time":"2024-05-14T09:13:52.0214638Z","parent_msg_id":"ed86ba30-95b2-4f31-a09f-6b71d98c8455"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 12, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+-------------+--------+-----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+--------------------+-------------+--------+-----+---------+\n|      877|Bike Wash - Disso...|      CL-9009|   false| null|   7.9500|\n|        3|     BB Ball Bearing|      BE-2349|    true| null|   0.0000|\n|      316|               Blade|      BL-2036|    true| null|   0.0000|\n|        2|        Bearing Ball|      BA-8327|   false| null|   0.0000|\n+---------+--------------------+-------------+--------+-----+---------+\n\n+---------+-----------+-------------+--------+-----+---------+\n|ProductID|       Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+-----------+-------------+--------+-----+---------+\n|      317|LL Crankarm|      CA-5965|   false|Black|   0.0000|\n|      318|ML Crankarm|      CA-6738|   false|Black|   0.0000|\n|      319|HL Crankarm|      CA-7457|   false|Black|   0.0000|\n+---------+-----------+-------------+--------+-----+---------+\n\n"]}],"execution_count":10,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"ca955473-fb99-452c-9a7e-6be528223d16"},{"cell_type":"code","source":["# wyłączenie wrażliwości na wielkość liter w foltrowaniu\n","\n","from pyspark.sql.functions import lower\n","from pyspark.sql.functions import col\n","\n","\n","df.filter(lower(col(\"Name\")).startswith(\"b\")).show()\n","\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":47,"statement_ids":[47],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T10:17:24.2722251Z","session_start_time":null,"execution_start_time":"2024-05-14T10:17:25.1699534Z","execution_finish_time":"2024-05-14T10:17:26.0437602Z","parent_msg_id":"a6d11068-5c80-4f12-9ba1-51d8048cfb40"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 47, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+-------------+--------+-----+---------+\n|ProductID|                Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+--------------------+-------------+--------+-----+---------+\n|      877|Bike Wash - Disso...|      CL-9009|   false| null|   7.9500|\n|        3|     BB Ball Bearing|      BE-2349|    true| null|   0.0000|\n|      316|               Blade|      BL-2036|    true| null|   0.0000|\n|        2|        Bearing Ball|      BA-8327|   false| null|   0.0000|\n+---------+--------------------+-------------+--------+-----+---------+\n\n"]}],"execution_count":45,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"99900465-7dd3-4a0c-a434-a66e9a380877"},{"cell_type":"code","source":["# isNull, isNotNull\n","\n","df.filter(df.Color.isNull()).show(5)\n","df.filter(df.Color.isNotNull()).show(5)"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":15,"statement_ids":[15],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:14:39.5129565Z","session_start_time":null,"execution_start_time":"2024-05-14T09:14:40.0407163Z","execution_finish_time":"2024-05-14T09:14:41.6450069Z","parent_msg_id":"93a83e20-1565-4d26-a838-f8ac28016131"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 15, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+-----------------+-------------+--------+-----+---------+\n|ProductID|             Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+-----------------+-------------+--------+-----+---------+\n|      994|LL Bottom Bracket|      BB-7421|    true| null|  53.9900|\n|      995|ML Bottom Bracket|      BB-8107|    true| null| 101.2400|\n|      996|HL Bottom Bracket|      BB-9108|    true| null| 121.4900|\n|      507|  LL Mountain Rim|      RM-M464|   false| null|   0.0000|\n|      508|  ML Mountain Rim|      RM-M692|   false| null|   0.0000|\n+---------+-----------------+-------------+--------+-----+---------+\nonly showing top 5 rows\n\n+---------+----------------+-------------+--------+-----+---------+\n|ProductID|            Name|ProductNumber|MakeFlag|Color|ListPrice|\n+---------+----------------+-------------+--------+-----+---------+\n|      759|Road-650 Red, 58|   BK-R50R-58|    true|  Red| 782.9900|\n|      760|Road-650 Red, 60|   BK-R50R-60|    true|  Red| 782.9900|\n|      761|Road-650 Red, 62|   BK-R50R-62|    true|  Red| 782.9900|\n|      762|Road-650 Red, 44|   BK-R50R-44|    true|  Red| 782.9900|\n|      763|Road-650 Red, 48|   BK-R50R-48|    true|  Red| 782.9900|\n+---------+----------------+-------------+--------+-----+---------+\nonly showing top 5 rows\n\n"]}],"execution_count":13,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"edf29fe1-9ca4-4c39-9136-54d76362a462"},{"cell_type":"code","source":["# when, alias dla większej czytelności\n","\n","from pyspark.sql.functions import when\n","\n","df.select(df.ProductID, \n","          df.Name,\n","          df.ListPrice,\n","          when(df.ListPrice<100,\"low\").when(df.ListPrice>300,\"high\").otherwise(\"middle\").alias(\"PriceInfo\")\n","          ).show(100)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"e28ecdf4-c3db-47e4-9320-fe4a50028c21","statement_id":19,"statement_ids":[19],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T09:17:35.5716901Z","session_start_time":null,"execution_start_time":"2024-05-14T09:17:36.0758361Z","execution_finish_time":"2024-05-14T09:17:36.9509281Z","parent_msg_id":"cb07218c-d6e1-431e-b0db-cae7a9ea021b"},"text/plain":"StatementMeta(, e28ecdf4-c3db-47e4-9320-fe4a50028c21, 19, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+--------------------+---------+---------+\n|ProductID|                Name|ListPrice|PriceInfo|\n+---------+--------------------+---------+---------+\n|      759|    Road-650 Red, 58| 782.9900|     high|\n|      760|    Road-650 Red, 60| 782.9900|     high|\n|      761|    Road-650 Red, 62| 782.9900|     high|\n|      762|    Road-650 Red, 44| 782.9900|     high|\n|      763|    Road-650 Red, 48| 782.9900|     high|\n|      764|    Road-650 Red, 52| 782.9900|     high|\n|      765|  Road-650 Black, 58| 782.9900|     high|\n|      766|  Road-650 Black, 60| 782.9900|     high|\n|      767|  Road-650 Black, 62| 782.9900|     high|\n|      768|  Road-650 Black, 44| 782.9900|     high|\n|      769|  Road-650 Black, 48| 782.9900|     high|\n|      770|  Road-650 Black, 52| 782.9900|     high|\n|      792|    Road-250 Red, 58|2443.3500|     high|\n|      793|  Road-250 Black, 44|2443.3500|     high|\n|      794|  Road-250 Black, 48|2443.3500|     high|\n|      795|  Road-250 Black, 52|2443.3500|     high|\n|      796|  Road-250 Black, 58|2443.3500|     high|\n|      977|  Road-750 Black, 58| 539.9900|     high|\n|      997|  Road-750 Black, 44| 539.9900|     high|\n|      998|  Road-750 Black, 48| 539.9900|     high|\n|      999|  Road-750 Black, 52| 539.9900|     high|\n|      749|    Road-150 Red, 62|3578.2700|     high|\n|      750|    Road-150 Red, 44|3578.2700|     high|\n|      751|    Road-150 Red, 48|3578.2700|     high|\n|      752|    Road-150 Red, 52|3578.2700|     high|\n|      753|    Road-150 Red, 56|3578.2700|     high|\n|      754|    Road-450 Red, 58|1457.9900|     high|\n|      755|    Road-450 Red, 60|1457.9900|     high|\n|      756|    Road-450 Red, 44|1457.9900|     high|\n|      757|    Road-450 Red, 48|1457.9900|     high|\n|      758|    Road-450 Red, 52|1457.9900|     high|\n|      789|    Road-250 Red, 44|2443.3500|     high|\n|      790|    Road-250 Red, 48|2443.3500|     high|\n|      791|    Road-250 Red, 52|2443.3500|     high|\n|      722|LL Road Frame - B...| 337.2200|     high|\n|      723|LL Road Frame - B...| 337.2200|     high|\n|      724|LL Road Frame - B...| 337.2200|     high|\n|      725|LL Road Frame - R...| 337.2200|     high|\n|      726|LL Road Frame - R...| 337.2200|     high|\n|      727|LL Road Frame - R...| 337.2200|     high|\n|      728|LL Road Frame - R...| 337.2200|     high|\n|      729|LL Road Frame - R...| 337.2200|     high|\n|      730|LL Road Frame - R...| 337.2200|     high|\n|      736|LL Road Frame - B...| 337.2200|     high|\n|      737|LL Road Frame - B...| 337.2200|     high|\n|      738|LL Road Frame - B...| 337.2200|     high|\n|      680|HL Road Frame - B...|1431.5000|     high|\n|      706|HL Road Frame - R...|1431.5000|     high|\n|      717|HL Road Frame - R...|1431.5000|     high|\n|      718|HL Road Frame - R...|1431.5000|     high|\n|      719|HL Road Frame - R...|1431.5000|     high|\n|      720|HL Road Frame - R...|1431.5000|     high|\n|      721|HL Road Frame - R...|1431.5000|     high|\n|      837|HL Road Frame - B...|1431.5000|     high|\n|      838|HL Road Frame - B...|1431.5000|     high|\n|      839|HL Road Frame - B...|1431.5000|     high|\n|      840|HL Road Frame - B...|1431.5000|     high|\n|      731|ML Road Frame - R...| 594.8300|     high|\n|      732|ML Road Frame - R...| 594.8300|     high|\n|      733|ML Road Frame - R...| 594.8300|     high|\n|      734|ML Road Frame - R...| 594.8300|     high|\n|      735|ML Road Frame - R...| 594.8300|     high|\n|      739|HL Mountain Frame...|1364.5000|     high|\n|      742|HL Mountain Frame...|1364.5000|     high|\n|      743|HL Mountain Frame...|1349.6000|     high|\n|      746|HL Mountain Frame...|1349.6000|     high|\n|      747|HL Mountain Frame...|1349.6000|     high|\n|      748|HL Mountain Frame...|1364.5000|     high|\n|      779|Mountain-200 Silv...|2319.9900|     high|\n|      780|Mountain-200 Silv...|2319.9900|     high|\n|      781|Mountain-200 Silv...|2319.9900|     high|\n|      782|Mountain-200 Blac...|2294.9900|     high|\n|      783|Mountain-200 Blac...|2294.9900|     high|\n|      784|Mountain-200 Blac...|2294.9900|     high|\n|      917|LL Mountain Frame...| 264.0500|   middle|\n|      918|LL Mountain Frame...| 264.0500|   middle|\n|      919|LL Mountain Frame...| 264.0500|   middle|\n|      920|LL Mountain Frame...| 264.0500|   middle|\n|      924|LL Mountain Frame...| 249.7900|   middle|\n|      925|LL Mountain Frame...| 249.7900|   middle|\n|      926|LL Mountain Frame...| 249.7900|   middle|\n|      927|LL Mountain Frame...| 249.7900|   middle|\n|      943|LL Mountain Frame...| 249.7900|   middle|\n|      944|LL Mountain Frame...| 264.0500|   middle|\n|      984|Mountain-500 Silv...| 564.9900|     high|\n|      985|Mountain-500 Silv...| 564.9900|     high|\n|      986|Mountain-500 Silv...| 564.9900|     high|\n|      987|Mountain-500 Silv...| 564.9900|     high|\n|      988|Mountain-500 Silv...| 564.9900|     high|\n|      989|Mountain-500 Blac...| 539.9900|     high|\n|      990|Mountain-500 Blac...| 539.9900|     high|\n|      991|Mountain-500 Blac...| 539.9900|     high|\n|      992|Mountain-500 Blac...| 539.9900|     high|\n|      993|Mountain-500 Blac...| 539.9900|     high|\n|      740|HL Mountain Frame...|1364.5000|     high|\n|      741|HL Mountain Frame...|1364.5000|     high|\n|      744|HL Mountain Frame...|1349.6000|     high|\n|      745|HL Mountain Frame...|1349.6000|     high|\n|      771|Mountain-100 Silv...|3399.9900|     high|\n|      772|Mountain-100 Silv...|3399.9900|     high|\n+---------+--------------------+---------+---------+\nonly showing top 100 rows\n\n"]}],"execution_count":17,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"4535e618-9de3-4a29-a05a-29a61620ae56"},{"cell_type":"code","source":["%%sql\n","SELECT ProductID, Name, ProductNumber, MakeFlag, Color, ListPrice\n","FROM Production_Product \n","WHERE Name LIKE '%Wheel' \n","ORDER BY ListPrice DESC    \n","LIMIT 5"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"eef46822-6e60-4b4b-9700-5983eb0c6618","statement_id":24,"statement_ids":[24],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T12:33:23.6406618Z","session_start_time":null,"execution_start_time":"2024-05-14T12:33:24.2131105Z","execution_finish_time":"2024-05-14T12:33:25.7377431Z","parent_msg_id":"d43d051e-3bd1-4754-9080-54e0b9e9ff4c"},"text/plain":"StatementMeta(, eef46822-6e60-4b4b-9700-5983eb0c6618, 24, Finished, Available)"},"metadata":{}},{"output_type":"execute_result","execution_count":22,"data":{"application/vnd.synapse.sparksql-result+json":{"schema":{"type":"struct","fields":[{"name":"ProductID","type":"long","nullable":true,"metadata":{}},{"name":"Name","type":"string","nullable":true,"metadata":{}},{"name":"ProductNumber","type":"string","nullable":true,"metadata":{}},{"name":"MakeFlag","type":"boolean","nullable":true,"metadata":{}},{"name":"Color","type":"string","nullable":true,"metadata":{}},{"name":"ListPrice","type":"decimal(19,4)","nullable":true,"metadata":{}}]},"data":[["828","HL Road Rear Wheel","RW-R820",true,"Black","357.0600"],["820","HL Road Front Wheel","FW-R820",true,"Black","330.0600"],["825","HL Mountain Rear Wheel","RW-M928",true,"Black","327.2150"],["817","HL Mountain Front Wheel","FW-M928",true,"Black","300.2150"],["827","ML Road Rear Wheel","RW-R762",true,"Black","275.3850"]]},"text/plain":"<Spark SQL result set with 5 rows and 6 fields>"},"metadata":{}}],"execution_count":22,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"sparksql","language_group":"synapse_pyspark"},"collapsed":false},"id":"22515403-e622-4969-9493-a026e816abc7"},{"cell_type":"code","source":["df.filter(df.Name.endswith(\"Wheel\")).sort(df.ListPrice.desc()).show(5, truncate=False)\n"],"outputs":[{"output_type":"display_data","data":{"application/vnd.livy.statement-meta+json":{"spark_pool":null,"session_id":"eef46822-6e60-4b4b-9700-5983eb0c6618","statement_id":25,"statement_ids":[25],"state":"finished","livy_statement_state":"available","queued_time":"2024-05-14T12:33:38.3779892Z","session_start_time":null,"execution_start_time":"2024-05-14T12:33:38.9218059Z","execution_finish_time":"2024-05-14T12:33:39.8102304Z","parent_msg_id":"bc945354-a103-4340-9fb7-acc2864221e3"},"text/plain":"StatementMeta(, eef46822-6e60-4b4b-9700-5983eb0c6618, 25, Finished, Available)"},"metadata":{}},{"output_type":"stream","name":"stdout","text":["+---------+-----------------------+-------------+--------+-----+---------+\n|ProductID|Name                   |ProductNumber|MakeFlag|Color|ListPrice|\n+---------+-----------------------+-------------+--------+-----+---------+\n|828      |HL Road Rear Wheel     |RW-R820      |true    |Black|357.0600 |\n|820      |HL Road Front Wheel    |FW-R820      |true    |Black|330.0600 |\n|825      |HL Mountain Rear Wheel |RW-M928      |true    |Black|327.2150 |\n|817      |HL Mountain Front Wheel|FW-M928      |true    |Black|300.2150 |\n|827      |ML Road Rear Wheel     |RW-R762      |true    |Black|275.3850 |\n+---------+-----------------------+-------------+--------+-----+---------+\nonly showing top 5 rows\n\n"]}],"execution_count":23,"metadata":{"jupyter":{"source_hidden":false,"outputs_hidden":false},"nteract":{"transient":{"deleting":false}},"microsoft":{"language":"python","language_group":"synapse_pyspark"}},"id":"616f8901-0f15-4a50-bf97-160ab2729c54"}],"metadata":{"language_info":{"name":"python"},"kernel_info":{"name":"synapse_pyspark"},"microsoft":{"language":"python","language_group":"synapse_pyspark","ms_spell_check":{"ms_spell_check_language":"en"}},"widgets":{},"kernelspec":{"name":"synapse_pyspark","language":"Python","display_name":"Synapse PySpark"},"nteract":{"version":"nteract-front-end@1.0.0"},"spark_compute":{"compute_id":"/trident/default"},"dependencies":{"lakehouse":{"default_lakehouse":"de6f32c6-ff57-4e70-baac-a89780d36482","default_lakehouse_name":"AdventureWorks","default_lakehouse_workspace_id":"1e8d4c96-f071-4b69-86dc-247ebd5e83d1"}}},"nbformat":4,"nbformat_minor":5}