Part IV Schemas.ipynb

{"cells":[{"cell_type":"markdown","source":["# Part IV: Data Schemas is Spark\n\nThis notebook covers some technical aspects of spark handles schemas from given data objects.\n\nWe will look at:\n1. Inferred schemas which are automatically discovered by Spark\n2. Explicit schemas which we provide to Spark \n\n**Data Scenario**\nFor this notebook we won't be using the data we gathered from yfinance. Instead, this scenario has us looking at client data. Customer data is important to every business and Spark is an excellent tool to analyze client data that may be very large."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"6ac291b2-3e5b-4c5e-8617-2694abe176d9","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["from pyspark.sql import SparkSession\nfrom pyspark.sql.types import Row\nfrom pyspark.sql.functions import regexp_replace, split"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"a690d3e9-559b-49cd-b0aa-049a4e7fbe93","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["spark = SparkSession.builder.appName(\"Spark Schemas\").getOrCreate()"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"d34040cf-9c01-4755-81eb-7e6c5b692c38","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"markdown","source":["## Using Databricks\n\nDatabricks makes it really easy to get move external data (like a csv) into the Databricks enviroment. \n\nYou can use the GUI to set all the properties needed and even get a preview of the data before you create a new table. Below we will be using pyspark and setting the read properties in our notebook.\n\nIf you are running your own Spark enviroment, you will load the data from the local directory."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"bf6c6664-382e-424b-8bb9-7d9892ea8845","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["# File location and type\nfile_location = \"/FileStore/tables/MOCK_CLIENT_DATA.csv\"\nfile_type = \"csv\"\n\n# CSV options\ninfer_schema = \"true\"\nfirst_row_is_header = \"true\"\ndelimiter = \",\"\n\n# The applied options are for CSV files. For other file types, these will be ignored.\ndf = spark.read.format(file_type) \\\n  .option(\"inferSchema\", infer_schema) \\\n  .option(\"header\", first_row_is_header) \\\n  .option(\"sep\", delimiter) \\\n  .load(file_location)\n\ndf.show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"09f5a6bf-dd6d-46b7-b9d0-4f30783299de","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+\n| id|  first_name|last_name|               email|account_balance|average_return|portfolio_theme|trade_frequency|last_contact_dt|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+\n|  1|        Bria|     Ruse|     bruse0@lulu.com|     $558243.81|         -4.53|           core|         Weekly|     2022-11-25|\n|  2|       Merry|   Effemy| meffemy1@paypal.com|       $3739.01|          8.09|     smart-beta|         Seldom|     2022-07-20|\n|  3|     Chrisse| Iannello|ciannello2@tinypi...|     $347550.67|         -3.58|     smart-beta|          Often|     2022-12-16|\n|  4|Lorettalorna| Bonnette|lbonnette3@dropbo...|     $250191.19|          4.37|           core|         Yearly|     2022-05-12|\n|  5|     Maxwell| Spellicy|mspellicy4@freewe...|     $705098.95|         -0.22|    sustainable|          Often|     2022-07-14|\n|  6|        Conn|  Bauduin|cbauduin5@ustream.tv|     $414601.73|          4.95|     smart-beta|         Yearly|     2023-01-15|\n|  7|       Rurik|Wolverson|rwolverson6@cnbc.com|     $183900.44|          1.53|    sustainable|         Seldom|     2023-03-31|\n|  8|       Pavia|   Gueste|pgueste7@dagondes...|     $824124.75|          0.14|           core|          Daily|     2023-02-07|\n|  9|     Prissie| Drescher|pdrescher8@forbes...|     $347858.05|          3.96|           core|         Yearly|     2022-12-22|\n| 10|  Batholomew|  Housbie|bhousbie9@postero...|      $26098.72|         -6.61|           core|        Monthly|     2022-02-11|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+\nonly showing top 10 rows\n\n"]}],"execution_count":0},{"cell_type":"code","source":["#here we can view the inferred schema\ndf.schema"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"cdd4909b-6f46-46ea-9a27-5350b99b8764","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["Out[32]: StructType([StructField('id', IntegerType(), True), StructField('first_name', StringType(), True), StructField('last_name', StringType(), True), StructField('email', StringType(), True), StructField('account_balance', StringType(), True), StructField('average_return', DoubleType(), True), StructField('portfolio_theme', StringType(), True), StructField('trade_frequency', StringType(), True), StructField('last_contact_dt', DateType(), True), StructField('numeric_balance', DoubleType(), True)])"]}],"execution_count":0},{"cell_type":"markdown","source":["You'll notice we let Spark infer the schema of our data. It was able to determine that id is an integer for example and last_contact_dt is a date. You may also notice that account_balance is a string. Why is this?\n\nSpark decided this coulmn is String data type because the data contains the \"$\" special character. So even though we would treat this data as a decimal number, Spark is smart enough to know that from a programming perspective, this data has to be a string. \n\n## Changing Data Types\nBecause we know we will be performing numerical calculations on the account_balance column, let's go ahead and create a new column that is the data type and format we need. \n\nFirst we will have to remove the \"$\" special character.\n\n**Maintaining Data Quality** <br>\nSomething we should consider before we start changing this data is what are the impacts. My removing the special character what information could be lost? \n\nWhat we can do to preserve the integrity of the data is when we remove the currency sign, we create another new column that contains the currency type. In this case when we remove a \"$\" we would have an entry of \"USD\" in the currency type column. \n\nSince we are creating a new column anyway for the account_balance and preserving the original, this isn't a requirement, but it is good to keep in mind."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"c63d0901-d8d7-4bc2-848f-f092188cade9","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["df = df.withColumn(\n    \"numeric_balance\", regexp_replace(\"account_balance\", \"\\$\", \"\").cast(\"double\")\n)\ndf.show(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"7c59343c-1cdf-4c68-8bb7-32897f530f44","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\n| id|  first_name|last_name|               email|account_balance|average_return|portfolio_theme|trade_frequency|last_contact_dt|numeric_balance|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\n|  1|        Bria|     Ruse|     bruse0@lulu.com|     $558243.81|         -4.53|           core|         Weekly|     2022-11-25|      558243.81|\n|  2|       Merry|   Effemy| meffemy1@paypal.com|       $3739.01|          8.09|     smart-beta|         Seldom|     2022-07-20|        3739.01|\n|  3|     Chrisse| Iannello|ciannello2@tinypi...|     $347550.67|         -3.58|     smart-beta|          Often|     2022-12-16|      347550.67|\n|  4|Lorettalorna| Bonnette|lbonnette3@dropbo...|     $250191.19|          4.37|           core|         Yearly|     2022-05-12|      250191.19|\n|  5|     Maxwell| Spellicy|mspellicy4@freewe...|     $705098.95|         -0.22|    sustainable|          Often|     2022-07-14|      705098.95|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\nonly showing top 5 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["Nice, we now have our data ready for analysis. This was pretty easy since Spark was able to infer pretty accurately the data types in our file. But what if we want more control over the data types. For example, in monetary calculations it is almost always best to use the BigDecimal data type from Java. This is because when dealing with money precision is a top concern. \n\nThe downside to using BigDecimal is it can be slower.\n\n## Explicit Schemas\n\nWhen defining a schema explicitly, we must provide the column name, the data type, and whether the column can be nullable."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"0ad9807d-2f49-4ee2-99f0-04009b541c7c","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["#import our data types\nfrom pyspark.sql.types import DecimalType, StructType, StructField, StringType, IntegerType, DateType, FloatType"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"998936a9-cc3f-4888-be95-86bd96f087c6","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["# define our schema\nfields = [\n    # Column name, Data Type, Is Nullable\n    StructField(\"id\", IntegerType(), True),\n    StructField(\"first_name\", StringType(), True),\n    StructField(\"last_name\", StringType(), True),\n    StructField(\"email\", StringType(), True),\n    StructField(\"account_balance\", StringType(), True),\n    StructField(\"average_return\", FloatType(), True),\n    StructField(\"portfolio_theme\", StringType(), True),\n    StructField(\"trade_frequency\", StringType(), True),\n    StructField(\"last_contact_dt\", DateType(), True),\n    StructField(\"numeric_balance\", DecimalType(), True),\n]\n\nexplicit_schema = StructType(fields)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"eab83210-7f0a-427c-bd12-94fd609d6c12","inputWidgets":{},"title":""}},"outputs":[],"execution_count":0},{"cell_type":"code","source":["# File location and type\nfile_location = \"/FileStore/tables/MOCK_CLIENT_DATA.csv\"\nfile_type = \"csv\"\n\n# CSV options\ninfer_schema = \"false\"\nfirst_row_is_header = \"true\"\ndelimiter = \",\"\n\n# The applied options are for CSV files. For other file types, these will be ignored.\ndf = (\n    spark.read.format(file_type)\n    .option(\"inferSchema\", infer_schema)\n    .option(\"header\", first_row_is_header)\n    .option(\"sep\", delimiter)\n    .schema(explicit_schema)\n    .load(file_location)\n)\n\ndf.show(10)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"e868cd65-6620-434e-85fc-32652a949c74","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\n| id|  first_name|last_name|               email|account_balance|average_return|portfolio_theme|trade_frequency|last_contact_dt|numeric_balance|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\n|  1|        Bria|     Ruse|     bruse0@lulu.com|     $558243.81|         -4.53|           core|         Weekly|     2022-11-25|           null|\n|  2|       Merry|   Effemy| meffemy1@paypal.com|       $3739.01|          8.09|     smart-beta|         Seldom|     2022-07-20|           null|\n|  3|     Chrisse| Iannello|ciannello2@tinypi...|     $347550.67|         -3.58|     smart-beta|          Often|     2022-12-16|           null|\n|  4|Lorettalorna| Bonnette|lbonnette3@dropbo...|     $250191.19|          4.37|           core|         Yearly|     2022-05-12|           null|\n|  5|     Maxwell| Spellicy|mspellicy4@freewe...|     $705098.95|         -0.22|    sustainable|          Often|     2022-07-14|           null|\n|  6|        Conn|  Bauduin|cbauduin5@ustream.tv|     $414601.73|          4.95|     smart-beta|         Yearly|     2023-01-15|           null|\n|  7|       Rurik|Wolverson|rwolverson6@cnbc.com|     $183900.44|          1.53|    sustainable|         Seldom|     2023-03-31|           null|\n|  8|       Pavia|   Gueste|pgueste7@dagondes...|     $824124.75|          0.14|           core|          Daily|     2023-02-07|           null|\n|  9|     Prissie| Drescher|pdrescher8@forbes...|     $347858.05|          3.96|           core|         Yearly|     2022-12-22|           null|\n| 10|  Batholomew|  Housbie|bhousbie9@postero...|      $26098.72|         -6.61|           core|        Monthly|     2022-02-11|           null|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\nonly showing top 10 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["Hmmm, now why is numeric_balance null? Remember, the numeric_balance column doesn't exist in our source data. We had to create it ourselves.\n\nSpark took our word for it that the data did contain that column but because no data actually existed, Spark just initialized a null column. Let's go ahead and create our numberic_balance column again and set the data type to DecimalType."],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"4bc4788a-3ac4-402b-9dbc-d953edd859cf","inputWidgets":{},"title":""}}},{"cell_type":"code","source":["df = df.withColumn(\n    \"numeric_balance\",\n    regexp_replace(\"account_balance\", \"\\$\", \"\").cast(DecimalType(18, 2)),\n)\ndf.schema"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"53d04f63-b8c2-4e30-8ca0-98bae34ca8df","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["Out[57]: StructType([StructField('id', IntegerType(), True), StructField('first_name', StringType(), True), StructField('last_name', StringType(), True), StructField('email', StringType(), True), StructField('account_balance', StringType(), True), StructField('average_return', FloatType(), True), StructField('portfolio_theme', StringType(), True), StructField('trade_frequency', StringType(), True), StructField('last_contact_dt', DateType(), True), StructField('numeric_balance', DecimalType(18,2), True)])"]}],"execution_count":0},{"cell_type":"code","source":["df.show(5)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{"rowLimit":10000,"byteLimit":2048000},"nuid":"b77d9183-8946-4a35-a831-926a7a01b3ba","inputWidgets":{},"title":""}},"outputs":[{"output_type":"stream","output_type":"stream","name":"stdout","text":["+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\n| id|  first_name|last_name|               email|account_balance|average_return|portfolio_theme|trade_frequency|last_contact_dt|numeric_balance|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\n|  1|        Bria|     Ruse|     bruse0@lulu.com|     $558243.81|         -4.53|           core|         Weekly|     2022-11-25|      558243.81|\n|  2|       Merry|   Effemy| meffemy1@paypal.com|       $3739.01|          8.09|     smart-beta|         Seldom|     2022-07-20|        3739.01|\n|  3|     Chrisse| Iannello|ciannello2@tinypi...|     $347550.67|         -3.58|     smart-beta|          Often|     2022-12-16|      347550.67|\n|  4|Lorettalorna| Bonnette|lbonnette3@dropbo...|     $250191.19|          4.37|           core|         Yearly|     2022-05-12|      250191.19|\n|  5|     Maxwell| Spellicy|mspellicy4@freewe...|     $705098.95|         -0.22|    sustainable|          Often|     2022-07-14|      705098.95|\n+---+------------+---------+--------------------+---------------+--------------+---------------+---------------+---------------+---------------+\nonly showing top 5 rows\n\n"]}],"execution_count":0},{"cell_type":"markdown","source":["And that's it! Now we know how to work with Schemas and data types in Spark.\n\nYou can read more about Spark's datatypes in the documentation here -> [Spark.apache.org/docs/data_types](https://spark.apache.org/docs/latest/api/python/reference/pyspark.sql/data_types.html)"],"metadata":{"application/vnd.databricks.v1+cell":{"showTitle":false,"cellMetadata":{},"nuid":"f94a1c0c-8778-4087-a84b-c69acad75c79","inputWidgets":{},"title":""}}}],"metadata":{"application/vnd.databricks.v1+notebook":{"notebookName":"Part IV Schemas","dashboards":[],"notebookMetadata":{"pythonIndentUnit":4},"language":"python","widgets":{},"notebookOrigID":244054791462511}},"nbformat":4,"nbformat_minor":0}