sql-server-table.ipynb

{"cells":[{"cell_type":"markdown","source":["##Variables"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"6a1551c2-6254-4ae3-90d1-1c60f18f39f8"}}},{"cell_type":"code","source":["user_login = dbutils.secrets.get(scope = \"eightfive-key-vault\", key = \"DBR-SQL-User-Login\")\nuser_password = dbutils.secrets.get(scope = \"eightfive-key-vault\", key = \"DBR-SQL-User-Password\")\n\nserver = \"eightfive-sql-***.database.windows.net\"\ndatabase = \"eightfive-sql-***\"\ntable_read = \"dbo.Iowa_Liquor_Sales\"\ntable_read_write = \"dbo.Iowa_Liquor_Sales_DBR\""],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"78bb202e-f2ff-4d93-9736-6d1d1a881160"}},"outputs":[],"execution_count":0},{"cell_type":"markdown","source":["##Post: Databricks — read/write to a SQL table"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"1dcd3143-78e6-43c2-8af8-d4ba39b6aa76"}}},{"cell_type":"code","source":["iowa_liquor_sales_df = spark.read.format(\"jdbc\") \\\n    .option(\"url\", f\"jdbc:sqlserver://{server};databaseName={database};\") \\\n    .option(\"dbtable\", table) \\\n    .option(\"user\", user_login) \\\n    .option(\"password\", user_password) \\\n    .option(\"driver\", \"com.microsoft.sqlserver.jdbc.SQLServerDriver\") \\\n    .load() \\\n    .limit(5)\n\ndisplay(iowa_liquor_sales_df)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"be350523-f16a-4e89-a426-030e5b052499"}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["from pyspark.sql.functions import col, lit\njdbcDF1 = jdbcDF.withColumn(\"ID\", col(\"ID\") + lit(100000))"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"71679e7d-e927-4a6c-a94b-3a9f0b448af6"}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["jdbcDF1.write.mode(\"append\").format(\"jdbc\") \\\n    .option(\"url\", f\"jdbc:sqlserver://{server};databaseName={database};\") \\\n    .option(\"dbtable\", table) \\\n    .option(\"user\", user_login) \\\n    .option(\"password\", user_password) \\\n    .option(\"driver\", \"com.microsoft.sqlserver.jdbc.SQLServerDriver\") \\\n    .save()"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"7626bdc5-328b-47cf-b1d8-de8d0f40822b"}},"outputs":[],"execution_count":0},{"cell_type":"markdown","source":["##Post: Databricks - reference a SQL table"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"1ab58675-59ad-4935-9d62-43142f070561"}}},{"cell_type":"code","source":["spark.sql(f\"\"\"\n    CREATE TABLE iowa.iowa_liquor_sales_DBR\n    USING org.apache.spark.sql.jdbc \n    OPTIONS (\n      url 'jdbc:sqlserver://{server};database=eightfive-sql-database', \n      dbtable '{table_read_write}', \n      user '{user_login}', \n      password '{user_password}' \n    )\n\"\"\"\n)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"59cb267a-7a70-4fdd-b9a1-6feb5259424b"}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["%sql\n\nINSERT INTO iowa.iowa_liquor_sales_dbr\nVALUES (150, 'S150', cast('2022-07-01' as date), 'Iowa', 'Iowa', 150)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3b91fc67-70fd-4771-b256-612155840456"}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["iowaDF = spark.table(\"iowa.iowa_liquor_sales_DBR\").filter(iowaDF.ID.isin([150, 151]))\ndisplay(iowaDF)"],"metadata":{"application/vnd.databricks.v1+cell":{"title":"","showTitle":false,"inputWidgets":{},"nuid":"3858eea2-b366-412d-8769-4f83079a6d7a"}},"outputs":[],"execution_count":0}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"sql-server-table","dashboards":[],"notebookMetadata":{"pythonIndentUnit":4},"language":"python","widgets":{},"notebookOrigID":964332856631395}},"nbformat":4,"nbformat_minor":0}