This the repository for project 1 of IDS706Data_Engineering_Systemm

In this project, I downloaded a Mall Customer Segmentation Dataset from Kaggle and upload to the Databricks. Then I connect my Github Codespaces with my Databricks cluster, wrote a function to excute SQL query, built a command line tool and finally a simple web app. By default, the query returns the spending score of customers with $50k annual income, and users can put any income number they are interested in and all the customers' spending score will be displayed.

The dataset comes from Kaggle. It contains customer information including Customer ID, age, gender, annual income and spending score from a supermarket. The spending score is assigned to each customer based on their spending nature and purchasing behavior. By analyzing the dataset, the supermarket owner can segment their customers and excute effective strategy accordingly.

Created four secrets in GitHub settings. The four secrets are DATABRICKS_HOST, DATABRICKS_HTTP_PATH, DATABRICKS_SERVER_HOSTNAME and DATABRICKS_TOKEN.

Then test the following code in Codespaces to check the connection.

databricks clusters list --output JSON | jq
databricks fs ls dbfs:/
databricks jobs list --output JSON | jq

The default sql query will return the spending score of customers whose annual income are $50,000. Using the querydb function, it will return the spending score of all customers whose annual income is the number assigned.
For example:

chmod +x query_mall_sql_db.py 
./query_mall_sql_db.py cli-query  --help
./query_mall_sql_db.py cli-query  --income "60"

The chmod command is used to manage file system access permissions on Unix and Unix-like systems, +x means to excute. chi-query is the function name. Type --help to see the instruction or type --income"int" to excute the sql query.

python fastapi-app.py 

After typing the code in the terminal, a new web page will open and the home page says "Welcome to my Databricks sql query!". Type "/query" at the end of the url, the web page will return the customer's spending score.