Home > Software design >  Table Does Not Exist Error When Using write_pandas
Table Does Not Exist Error When Using write_pandas

Time:04-01

I have a very frustrating issue. At the bottom of this post is a function I created to (1) create a table in snowflake and (2) store a dataframe to that table.

The creation of the table is work fine. The issue is happening specifically with writepandas the code snippet:

    write_pandas(
                conn=conn,
                df=df,
                table_name=table_name,
                database=database,
                schema=schema
            )

I keep getting an error that the table I created "doesn't exist" because the naming convention is off .. for instance in the database the table is created as "DATABASE"."SCHEMA"."TABLE" but the error message says 'DATABASE.SCHEMA."TABLE"' does not exist

I know this is a simple issue but Im stuck for the moment. Any help would be appreciated.

from datetime import datetime, timedelta, date
from airflow import DAG
from airflow.providers.snowflake.operators.snowflake import SnowflakeOperator
from sqlalchemy import create_engine
import requests
from pandas.io.json import json_normalize 
import numpy as np
from sqlalchemy.types import Integer, Text, String, DateTime
from IPython.display import display, HTML
from flatten_json import flatten
from snowflake.connector import connect
from snowflake.connector.pandas_tools import write_pandas
from airflow.operators.python_operator import PythonOperator
import os
from airflow.providers.snowflake.hooks.snowflake import SnowflakeHook
import pandas as pd
from sklearn.preprocessing import MinMaxScaler

def create_store_snowflake(df,table):
    #quick transforms 
    df = df.rename(columns=str.upper)

    df.columns = df.columns.str.replace('[-,/]','')
    #Define the table name, schema, and database you want to write to
    #Note: the table, schema, and database need to already exist in Snowflake
    
    #Define the table name, schema, and database you want to write to

    table_name = table
    schema = 'schema'
    database = 'database'

    #Connect to Snowflake using the required user

    conn = connect(
            user="user",
            password="password",
            account="account",
            role="role",
            database = "database",
            schema = 'schema'
        )

    #reroute raw data to dataframe variable
    dataframe = df

    #Create the SQL statement to create or replace the table

    create_tbl_statement = "CREATE OR REPLACE TABLE "   database   "."   schema   "."   table_name   " (\n"

    # Loop through each column finding the datatype and adding it to the statement

    for column in dataframe.columns:
        if (
        dataframe[column].dtype.name == "int"
        or dataframe[column].dtype.name == "int64"
        ):
            create_tbl_statement = create_tbl_statement   column   " int"
        elif dataframe[column].dtype.name == "object":
            create_tbl_statement = create_tbl_statement   column   " varchar(16777216)"
        elif dataframe[column].dtype.name == "datetime64[ns]":
            create_tbl_statement = create_tbl_statement   column   " datetime"
        elif dataframe[column].dtype.name == "float64":
            create_tbl_statement = create_tbl_statement   column   " float8"
        elif dataframe[column].dtype.name == "bool":
            create_tbl_statement = create_tbl_statement   column   " boolean"
        else:
            create_tbl_statement = create_tbl_statement   column   " varchar(16777216)"

        # If column is not last column, add comma, else end sql-query
        if dataframe[column].name != dataframe.columns[-1]:
            create_tbl_statement = create_tbl_statement   ",\n"
        else:
            create_tbl_statement = create_tbl_statement   ")"

    #Execute the SQL statement to create the table

    conn.cursor().execute(create_tbl_statement)

    print(f"{table_name} created!")

    #write df to created table
    write_pandas(
                conn=conn,
                df=df,
                table_name=table_name,
                database=database,
                schema=schema
            )

    print(df.shape[0],f"rows written to {table_name} in Snowflake")

CodePudding user response:

just had to make sure the tablename was CAPITALIZED as everything stored to Snowflake is apparently capitalized ::face-palm:: instead of create_store_snowflake(df,'mynewtable') it has to be create_store_snowflake(df,'MYNEWTABLE')

CodePudding user response:

When the table identifier is wrapped with " during creation the followin rules applies:

create_tbl_statement= "CREATE OR REPLACE TABLE "   database   "."   schema   "."   table_name 

Double-quoted Identifiers:

Delimited identifiers (i.e. identifiers enclosed in double quotes) are case-sensitive and can start with and contain any valid characters

Important

If an object is created using a double-quoted identifier, when referenced in a query or any other SQL statement, the identifier must be specified exactly as created, including the double quotes. Failure to include the quotes might result in an Object does not exist error (or similar type of error).

  • Related