Snowflake: PySpark External Tables (On-Demand FGs)

Snowflake (On-Demand) Feature Group

This tutorial notebook will show how you can define an external feature group for a table in Snowflake.

In this notebook we assume that you already have snowflake account and did the getting started with snowflake tutorial.

import hsfs
Starting Spark application
IDYARN Application IDKindStateSpark UIDriver log
1application_1614610677629_0002pysparkidleLinkLink
SparkSession available as 'spark'.
connection = hsfs.connection()
# Get the feature store handle for the project's feature store
fs = connection.get_feature_store()
Connected. Call `.close()` to terminate connection gracefully.
snowflake_conn = fs.get_storage_connector("sfconnector")
snowflake_conn.spark_options()
sfOptions = snowflake_conn.spark_options()
sfOptions["query"] = "select * from TELCO"
df = spark.read.format("net.snowflake.spark.snowflake") \
  .options(**sfOptions) \
  .load()
df.show(10)
+-----------+------+--------------+-------+----------+------+-------------+----------------+----------------+---------------+-------------+-----------------+------------+------------+----------------+--------------+-----------------+--------------------+---------------+-------------+-----+
|CUSTOMER_ID|GENDER|SENIOR_CITIZEN|PARTNER|DEPENDENTS|TENURE|PHONE_SERVICE|  MULTIPLE_LINES|INTERNET_SERVICE|ONLINE_SECURITY|ONLINE_BACKUP|DEVICE_PROTECTION|TECH_SUPPORT|STREAMING_TV|STREAMING_MOVIES|      CONTRACT|PAPERLESS_BILLING|      PAYMENT_METHOD|MONTHLY_CHARGES|TOTAL_CHARGES|CHURN|
+-----------+------+--------------+-------+----------+------+-------------+----------------+----------------+---------------+-------------+-----------------+------------+------------+----------------+--------------+-----------------+--------------------+---------------+-------------+-----+
| 7590-VHVEG|Female|         false|    Yes|        No|     1|           No|No phone service|             DSL|             No|          Yes|               No|          No|          No|              No|Month-to-month|              Yes|    Electronic check|          29.85|        29.85|   No|
| 5575-GNVDE|  Male|         false|     No|        No|    34|          Yes|              No|             DSL|            Yes|           No|              Yes|          No|          No|              No|      One year|               No|        Mailed check|          56.95|       1889.5|   No|
| 3668-QPYBK|  Male|         false|     No|        No|     2|          Yes|              No|             DSL|            Yes|          Yes|               No|          No|          No|              No|Month-to-month|              Yes|        Mailed check|          53.85|       108.15|  Yes|
| 7795-CFOCW|  Male|         false|     No|        No|    45|           No|No phone service|             DSL|            Yes|           No|              Yes|         Yes|          No|              No|      One year|               No|Bank transfer (au...|           42.3|      1840.75|   No|
| 9237-HQITU|Female|         false|     No|        No|     2|          Yes|              No|     Fiber optic|             No|           No|               No|          No|          No|              No|Month-to-month|              Yes|    Electronic check|           70.7|       151.65|  Yes|
| 9305-CDSKC|Female|         false|     No|        No|     8|          Yes|             Yes|     Fiber optic|             No|           No|              Yes|          No|         Yes|             Yes|Month-to-month|              Yes|    Electronic check|          99.65|        820.5|  Yes|
| 1452-KIOVK|  Male|         false|     No|       Yes|    22|          Yes|             Yes|     Fiber optic|             No|          Yes|               No|          No|         Yes|              No|Month-to-month|              Yes|Credit card (auto...|           89.1|       1949.4|   No|
| 6713-OKOMC|Female|         false|     No|        No|    10|           No|No phone service|             DSL|            Yes|           No|               No|          No|          No|              No|Month-to-month|               No|        Mailed check|          29.75|        301.9|   No|
| 7892-POOKP|Female|         false|    Yes|        No|    28|          Yes|             Yes|     Fiber optic|             No|           No|              Yes|         Yes|         Yes|             Yes|Month-to-month|              Yes|    Electronic check|          104.8|      3046.05|  Yes|
| 6388-TABGU|  Male|         false|     No|       Yes|    62|          Yes|              No|             DSL|            Yes|          Yes|               No|          No|          No|              No|      One year|               No|Bank transfer (au...|          56.15|      3487.95|   No|
+-----------+------+--------------+-------+----------+------+-------------+----------------+----------------+---------------+-------------+-----------------+------------+------------+----------------+--------------+-----------------+--------------------+---------------+-------------+-----+
only showing top 10 rows

External (On-Demand) Feature Group

An on-Demand Feature Group stores the metadata for features in the feature store. The actual feature data is read from the external source using a storage connector. The next cell shows how to create an on-demand feature group using the snowflake connector.

telco_on_dmd = fs.create_on_demand_feature_group(name="telco_snowflake_scala",
                                                version=2,
                                                query="select * from telco",
                                                description="On-demand feature group for telecom customer data",
                                                storage_connector=snowflake_conn,
                                                statistics_config=True)
telco_on_dmd.save()
telco_on_dmd.select(['customer_id', 'internet_service', 'phone_service', 'total_charges', 'churn']).show(5)
+-----------+----------------+-------------+-------------+-----+
|customer_id|internet_service|phone_service|total_charges|churn|
+-----------+----------------+-------------+-------------+-----+
| 7590-VHVEG|             DSL|           No|        29.85|   No|
| 5575-GNVDE|             DSL|          Yes|       1889.5|   No|
| 3668-QPYBK|             DSL|          Yes|       108.15|  Yes|
| 7795-CFOCW|             DSL|           No|      1840.75|   No|
| 9237-HQITU|     Fiber optic|          Yes|       151.65|  Yes|
+-----------+----------------+-------------+-------------+-----+
only showing top 5 rows