ProgrammingError: 001003 (42000): SQL compilation error: syntax error line 11 at position 2 unexpected '2'. syntax error line 12 at position 2 unexpected '3'

Using pyrasgo
, ,
1

I am using pyrasgo version 1.4.4

I am submitting code like:

for d in datas:
    df = pd.read_csv(Path(data_dir) / 'baseball' / d['csv'])
    df = reduce_mem_usage(df)
    if '2B' in df.columns and '3B' in df.columns:
        df.rename(columns={'2B':'_2B','3B':'_3B'}, inplace=True)
    rasgo.publish.df(df=df,
                     name=d['name'],
                     description=d['descr'],
                     resource_key=d['tbl'],
                     dataset_table_name=d['tbl'])

I am getting an error that says:

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 11 at position 2 unexpected '2'.
syntax error line 12 at position 2 unexpected '3'.

Full trace is:

ProgrammingError                          Traceback (most recent call last)
/var/folders/05/8zwf5qz57sd9y47_pnvpysfh0000gn/T/ipykernel_10536/270276547.py in <module>
     35     df = pd.read_csv(Path(data_dir) / 'baseball' / d['csv'])
     36     df = reduce_mem_usage(df)
---> 37     rasgo.publish.df(df=df,
     38                      name=d['name'],
     39                      description=d['descr'],

~/opt/anaconda3/lib/python3.9/site-packages/pyrasgo/api/publish.py in df(self, df, name, resource_key, description, dataset_table_name, parents, verbose, attributes, fqtn, if_exists, generate_stats)
    256 
    257             df_utils.cleanse_sql_dataframe(df)
--> 258             self.data_warehouse.write_dataframe_to_table(df, table_name=table_name, append=False)
    259             self.data_warehouse.grant_table_ownership(table=table_name, role=self.data_warehouse.publisher_role)
    260             self.data_warehouse.grant_table_access(table=table_name, role=self.data_warehouse.reader_role)

~/opt/anaconda3/lib/python3.9/site-packages/pyrasgo/storage/datawarehouse/base.py in write_dataframe_to_table(self, df, table_name, append)
    157     ):
    158         with self.user_connection.cursor() as cursor:
--> 159             cursor.execute(dfutils.generate_ddl(df=df, table_name=table_name, append=append))
    160         self._write_dataframe_to_table(df=df, table_name=table_name, append=append)
    161 

~/opt/anaconda3/lib/python3.9/site-packages/snowflake/connector/cursor.py in execute(self, command, params, _bind_stage, timeout, _exec_async, _do_reset, _put_callback, _put_azure_callback, _put_callback_output_stream, _get_callback, _get_azure_callback, _get_callback_output_stream, _show_progress_bar, _statement_params, _is_internal, _describe_only, _no_results, _use_ijson, _is_put_get, _raise_put_get_error, _force_put_overwrite, file_stream)
    787             )  # NULL result in a non-nullable column
    788             error_class = IntegrityError if is_integrity_error else ProgrammingError
--> 789             Error.errorhandler_wrapper(self.connection, self, error_class, errvalue)
    790         return self
    791 

~/opt/anaconda3/lib/python3.9/site-packages/snowflake/connector/errors.py in errorhandler_wrapper(connection, cursor, error_class, error_value)
    271         """
    272 
--> 273         handed_over = Error.hand_to_other_handler(
    274             connection,
    275             cursor,

~/opt/anaconda3/lib/python3.9/site-packages/snowflake/connector/errors.py in hand_to_other_handler(connection, cursor, error_class, error_value)
    326         if cursor is not None:
    327             cursor.messages.append((error_class, error_value))
--> 328             cursor.errorhandler(connection, cursor, error_class, error_value)
    329             return True
    330         elif connection is not None:

~/opt/anaconda3/lib/python3.9/site-packages/snowflake/connector/errors.py in default_errorhandler(connection, cursor, error_class, error_value)
    205             A Snowflake error.
    206         """
--> 207         raise error_class(
    208             msg=error_value.get("msg"),
    209             errno=error_value.get("errno"),

ProgrammingError: 001003 (42000): SQL compilation error:
syntax error line 11 at position 2 unexpected '2'.
syntax error line 12 at position 2 unexpected '3'.
2

This is due to your CSV having headers with start with a number (2B, 3B, etc) - this is causing your RDBMS to fail because Snowflake does not allow that.

We are implementing an automatic rename to add an underscore as a prefix, in this case.

3

This topic was automatically closed after 365 days. New replies are no longer allowed.