I have a dataframe in Synapse Apache spark pool and I want to create a table for that dataframe in my dedicated sql pool

Aditya Singh 105

I have a dataframe in Synapse Apache spark pool and I want to create a table for that dataframe in my dedicated sql pool.

User's image

Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-03-01T11:04:29.6866667+00:00
Hi @Aditya Singh

Thank you for using Microsoft Q&A platform and thanks for your question.

To create a table for a dataframe in Synapse Apache Spark pool and then create a table for that dataframe in a dedicated SQL pool, you can follow these general steps:

Load the data from the dataframe in Synapse Apache Spark pool into a temporary table in the dedicated SQL pool using PolyBase or T-SQL commands. You can use the CREATE TABLE AS SELECT statement to create the temporary table and load the data into it.

Once the data is loaded into the temporary table, you can create a new table in the dedicated SQL pool using the CREATE TABLE statement and specify the columns and data types based on the data in the temporary table.

Finally, you can use the INSERT INTO statement to insert the data from the temporary table into the new table in the dedicated SQL pool.

Here's an example of how you can load data from a temporary table into a new table in the dedicated SQL pool using T-SQL commands:

-- Create a temporary table in the dedicated SQL pool CREATE TABLE #temp_table ( column1 datatype1, column2 datatype2, ... ); -- Load data from the dataframe in Synapse Apache Spark pool into the temporary table INSERT INTO #temp_table SELECT * FROM OPENROWSET( BULK 'path_to_datafile', FORMAT = 'CSV', FIELDTERMINATOR = ',', ROWTERMINATOR = '\n', FIRSTROW = 2 ) AS data; -- Create a new table in the dedicated SQL pool based on the structure of the temporary table CREATE TABLE new_table ( column1 datatype1, column2 datatype2, ... ); -- Insert data from the temporary table into the new table INSERT INTO new_table SELECT * FROM #temp_table;

Note that the exact commands and syntax may vary depending on your specific scenario and data structure. Also, make sure to follow best practices for designing tables in Synapse SQL, such as choosing appropriate data types and distribution keys.

I hope this helps! Let me know if you have any further questions.
Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-03-03T16:25:34.6866667+00:00

@Aditya Singh We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Accepted answer

Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-03-04T05:54:52.5233333+00:00

Hi @Aditya Singh ,

I'm glad that you were able to resolve your issue and thank you for posting your solution so that others experiencing the same thing can easily reference this! Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others ", I'll repost your solution in case you'd like to accept the answer .

Issue: I have a dataframe in Synapse Apache spark pool and I want to create a table for that dataframe in my dedicated sql pool.

Solution: I got the solution which is below I have the pyspark dataframe df and using this dataframe I want to create a table in my dedicated pool under stage schema.

If I missed anything please let me know and I'd be happy to add it to my answer, or feel free to comment below with any additional information.

I hope this helps!

If you have any other questions, please let me know. Thank you again for your time and patience throughout this issue.

Please don’t forget to Accept Answer and Yes for "was this answer helpful" wherever the information provided helps you, this can be beneficial to other community members.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

1 additional answer

Aditya Singh 105 Reputation points

2024-03-04T05:30:52.21+00:00

Hi @Smaran Thoomu ,

I got the solution which is below
I have the pyspark dataframe df and using this dataframe I want to create a table in my dedicated pool under stage schema.
Please sign in to rate this answer.
Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-03-04T05:55:59.9533333+00:00

@Aditya Singh Glad to know your issue has been resolved. Since the Microsoft Q&A community has a policy that "The question author cannot accept their own answer. They can only accept answers by others "I'll repost your solution in case you'd like to accept the answer.
Sign in to comment