topic Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ? in Data Engineering

Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

BasavarajAngadi — Mon, 14 Feb 2022 04:43:11 GMT

If we use two different clusters one for pyspark code for transformation and one for SQL analytics . how to make permenant tables derived from pyspark code make available for running queries in databricks SQL analytics

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

AmanSehgal — Mon, 14 Feb 2022 06:24:04 GMT

You can write your ETL logic in notebooks, run the notebook over a cluster and write the data to a location where your S3 bucket is mounted.

Next, you can register that table with Hive MetaStore and access the same table in Databricks SQL.

To see the table, go to Data tab and select your schema/database to see registered tables.

Two ways to do this:

Option 1:

df.write.option("path",<s3-path-of-table>).saveAsTable(tableName)

Option 2

%python
df.write.save(<s3-path-of-table>)
 
%sql
CREATE TABLE <table-name>
USING DELTA
LOCATION <s3-path-of-table>

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

BasavarajAngadi — Mon, 14 Feb 2022 11:05:06 GMT

@Aman Sehgal so basically you are telling to write the transformed data from Databricks pyspark into ADLS gen2 and then use Data bricks SQL analytics to do below what you said ...

%sql
CREATE TABLE <table-name>
USING DELTA
LOCATION <s3-path-of-table>

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

AmanSehgal — Mon, 14 Feb 2022 13:11:01 GMT

Right.. Databricks is a platform to perform transformations.. Ideally your should either mount s3 bucket or ADLS gen 2 location in DBFS..

Read/Write/Update/Delete your data and to run SQL analytics from SQL tab, you'll have to register a table and start an endpoint..

You can also query the data via notebooks by using SQL in a cell. The only difference is, you'll have to spin up a cluster instead of an endpoint.

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

BasavarajAngadi — Tue, 15 Feb 2022 09:09:44 GMT

@Aman Sehgal you are making me confused ....we need to spin up the cluster if we use SQL end point right ?

and Can we not use magic commands "%Sql" within same notebook to write the pyspark data to SQL end point as table ?

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

AmanSehgal — Tue, 15 Feb 2022 13:58:43 GMT

When you're in Data Engineering tab in workspace, then you need to spin up a cluster. After spinning up the cluster, you can create a notebook and use %sql to write SQL command and query your table.

When you're in SQL tab in workspace, then you need to spin up a SQL Endpoint. After spinning an end point, go to Queries tab and you can write a SQL query to query your tables.

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

BasavarajAngadi — Fri, 18 Feb 2022 16:03:34 GMT

@Aman Sehgal Can we write data from data engineering workspace to SQL end point in databricks?

Re: Hi Experts , I am new to databricks. I want to know how to copy pyspark data into databricks SQL analytics ?

AmanSehgal — Sat, 19 Feb 2022 23:39:39 GMT

You can write data to a table (eg. default.my_table) and consume data from same table using SQL end point.