sql quiries

Overview This notebook will show you how to create and query a table or DataFrame that you uploaded to DBFS. DBFS is a Databricks File System that allows you to store data for querying inside of Databricks. This notebook assumes that you have a file already inside of DBFS that you would like to read from.

This notebook is written in Python so the default cell type is Python. However, you can use different languages by using the %LANGUAGE syntax. Python, Scala, SQL, and R are all supported.

In [2]: # File location and type file_location = "/FileStore/tables/users.csv" file_type = "csv"

# CSV options infer_schema = "false" first_row_is_header = "true" delimiter = ","

# The applied options are for CSV files. For other file types, these will be ignored. df = spark.read.format(file_type) \ .option("inferSchema", infer_schema) \ .option("header", first_row_is_header) \ .option("sep", delimiter) \ .load(file_location)

display(df)

In [3]: # Create a view or table

temp_table_name = "users_csv"

df.createOrReplaceTempView(temp_table_name)

In [4]: %sql

/* Query the created temp table in a SQL cell */

select * from `users_csv` LIMIT 5

In [5]: # With this registered as a temp view, it will only be available to this particular noteboo k. If you'd like other users to be able to query this table, you can also create a table fro m the DataFrame. # Once saved, this table will persist across cluster restarts as well as allow various users across different notebooks to query this data. # To do so, choose your table name and uncomment the bottom line.

permanent_table_name = "users_csv"

# df.write.format("parquet").saveAsTable(permanent_table_name)

In [6]: %sql

/* Users created and activated */

SELECT DATE_TRUNC('day',created_at) AS day, COUNT(*) AS all_users, COUNT(CASE WHEN activated_at IS NOT NULL THEN u.user_id ELSE NULL END) AS activated_

users FROM `users_csv` u WHERE created_at >= '2014-06-01' AND created_at < '2014-09-01' GROUP BY 1 ORDER BY 1

In [7]: %sql

/* Users created and NOT activated */

SELECT DATE_TRUNC('day',created_at) AS day, COUNT(*) AS all_users, COUNT(CASE WHEN activated_at IS NULL THEN u.user_id ELSE NULL END) AS not_activated_

users FROM `users_csv` u WHERE created_at >= '2014-06-01' AND created_at < '2014-09-01' GROUP BY 1 ORDER BY 1

user_id created_at company_id language activated_at state

0.0 2013-01-01 20:59:39 5737.0 english 2013-01-01 21:01:07 active

1.0 2013-01-01 13:07:46 28.0 english null pending

2.0 2013-01-01 10:59:05 51.0 english null pending

3.0 2013-01-01 18:40:36 2800.0 german 2013-01-01 18:42:02 active

4.0 2013-01-01 14:37:51 5110.0 indian 2013-01-01 14:39:05 active

5.0 2013-01-01 13:39:51 2463.0 spanish null pending

6.0 2013-01-01 18:37:27 11699.0 english 2013-01-01 18:38:45 active

7.0 2013-01-01 16:19:01 4765.0 french 2013-01-01 16:20:28 active

8.0 2013-01-01 04:38:30 2698.0 french 2013-01-01 04:40:10 active

9.0 2013-01-01 08:04:17 1.0 french null pending

10 0 2013 01 01 09:36:41 10 0 arabic null pending

user_id created_at company_id language activated_at state

0.0 2013-01-01 20:59:39 5737.0 english 2013-01-01 21:01:07 active

1.0 2013-01-01 13:07:46 28.0 english null pending

2.0 2013-01-01 10:59:05 51.0 english null pending

3.0 2013-01-01 18:40:36 2800.0 german 2013-01-01 18:42:02 active

4.0 2013-01-01 14:37:51 5110.0 indian 2013-01-01 14:39:05 active

day all_users activated_users

2014-06-01T00:00:00.000+0000 23 11

2014-06-02T00:00:00.000+0000 69 33

2014-06-03T00:00:00.000+0000 63 29

2014-06-04T00:00:00.000+0000 71 44

2014-06-05T00:00:00.000+0000 76 32

2014-06-06T00:00:00.000+0000 72 39

2014-06-07T00:00:00.000+0000 17 8

2014-06-08T00:00:00.000+0000 22 12

2014-06-09T00:00:00.000+0000 70 34

2014-06-10T00:00:00.000+0000 75 28

2014 06 11T00:00:00 000+0000 78 37

day all_users not_activated_users

2014-06-01T00:00:00.000+0000 23 12

2014-06-02T00:00:00.000+0000 69 36

2014-06-03T00:00:00.000+0000 63 34

2014-06-04T00:00:00.000+0000 71 27

2014-06-05T00:00:00.000+0000 76 44

2014-06-06T00:00:00.000+0000 72 33

2014-06-07T00:00:00.000+0000 17 9

2014-06-08T00:00:00.000+0000 22 10

2014-06-09T00:00:00.000+0000 70 36