Need to load the data from databricks to Snowflake table having ID,which automatically increments

ms_221
New Contributor II

‎11-11-2024 05:50 AM

I want to load the data from  df (say 3 columns c1,c2,c3) into the snowflake table say (test1) having columns (c1,c2,c3) and ID autoincrement column.

The df and snowflake table (test1) have same column definition and same datatypes. In the target table (test2) we have additional column say ID .

 

0 Kudos

Walter_C
Databricks Employee
Databricks Employee

‎11-13-2024 10:42 AM

To load data from a DataFrame into a Snowflake table with an autoincrement ID column, you can follow these steps:

  1. First, ensure that your Snowflake table (test1) is created with an autoincrement ID column:

    CREATE OR REPLACE TABLE test1 (
    ID INT AUTOINCREMENT START 1 INCREMENT 1,
    c1 VARCHAR,
    c2 VARCHAR,
    c3 VARCHAR
);​
  2. Then, use the write_pandas method from the Snowflake Connector for Python to load the data from your DataFrame into the Snowflake table. Here's how you can do it:
    import pandas as pd
from snowflake.connector.pandas_tools import write_pandas

# Assuming you have already established a connection to Snowflake
# and have a cursor object named 'cursor'

# Your DataFrame
df = pd.DataFrame({
    'c1': ['value1', 'value2', 'value3'],
    'c2': ['value4', 'value5', 'value6'],
    'c3': ['value7', 'value8', 'value9']
})

# Write the DataFrame to Snowflake
success, nchunks, nrows, _ = write_pandas(
    conn=cursor.connection,
    df=df,
    table_name='test1',
    database='your_database',
    schema='your_schema'
)

if success:
    print(f"Successfully loaded {nrows} rows into {nchunks} chunks")
else:
    print("Data load failed")
0 Kudos