For an instructor lead, in-depth look at learning SQL click below.
With increasing amounts of data generated every day, SQL has become a critical skill for data analysis and predictive analytics. Databricks SQL offers a seamless, unified platform for data analysts, data scientists and business users to interactively run queries and create rich visualizations. It also enables the execution of ML models. Let’s see how we can use Databricks SQL platform to build predictive models using MLlib, the machine learning library in Spark.
Querying data with Databricks SQL
First, we need to fetch the data from our database. Let’s assume we have a database named ‘salesdb’ with a table ‘sales_data’ that contains information about purchase transactions.
|
1 2 3 |
SELECT * FROM salesdb.sales_data; |
Preparing data for ML algorithms
Most ML algorithms require numerical input data, so we need to preprocess our data. For this, feature extractors are used. Assuming that our table has a column ‘purchase_category’ of type string, we will use StringIndexer to transform it into a column of category indices.
|
1 2 3 4 5 6 7 |
from pyspark.ml.feature import StringIndexer indexer = StringIndexer(inputCol="purchase_category", outputCol="categoryIndex") indexed = indexer.fit(df).transform(df) indexed.show() |
Building the Model
We will make use of MLlib, Apache Spark’s scalable machine learning library, to create our predictive model. We are going to use a Logistic Regression model as an example.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
from pyspark.ml.classification import LogisticRegression # Load training data training = spark.read.format("libsvm").load("data/mllib/sample_libsvm_data.txt") lr = LogisticRegression(maxIter=10, regParam=0.3, elasticNetParam=0.8) # Fit the model lrModel = lr.fit(training) # Print the coefficients and intercept for logistic regression print("Coefficients: " + str(lrModel.coefficients)) print("Intercept: " + str(lrModel.intercept)) |
ML for Predictive Analytics
Once we have the model, we can use it to make predictions or build predictive analytics. Here’s an example:
|
1 2 3 4 5 6 7 8 9 10 |
# Test data test = spark.read.format("libsvm").load("data/mllib/sample_libsvm_data.txt") # Use the model above to predict predictions = lrModel.transform(test) # Show the predictions predictions.show() |
In conclusion, Databricks SQL provides a powerful interface for harnessing the power of data for predictive analytics. When combined with a capable ML library like MLlib, it becomes a potent tool for delivering insights from your data.