dbt seed never completing

[colin-rogers-dbt]

Describe the bug

Running dbt seed with a number of files >50k rows does not finish despite all jobs having completed on the cluster and connect_timeout value being set in the profile.

Steps To Reproduce

run dbt seed with a number of large files.

Expected behavior

Succeed or fail

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

System information

tested on

dbt=1.5.7
databricks=1.5.6

The operating system you're using:
ubuntu 22:04

The output of python --version:
py38

Additional context

Add any other context about the problem here.

[benc-db]

Thanks for filing the bug @colin-rogers-dbt.

[nrichards17]

I've also experienced large seeds not completing, particularly with CSVs with >10k rows. It also takes upwards of 5 minutes to load a seed with ~7k rows. However, if you provide the column_types in your schema.yml file, the load times for seeds drops dramatically.

This is because the databricks__load_csv_rows macro is extremely slow if you don't provide column_types and instead rely on the inferred types. The macro is iterating over all rows and columns in the CSV in this double nested loop and performing type lookup for every value (!) if the column type is not specified.

The underlying agate_table object already has column type information and the line below is just doing a lookup of said type for that column:

{%- set inferred_type = adapter.convert_type(agate_table, loop.index0) -%}

As far as I can tell, there is no reason why this has to be done for every cell; a dict containing the column-level type inference could be constructed at the top, similar to:

{% set column_override = model['config'].get('column_types', {}) %}

It's not something you notice when seeds are small, but you definitely start to feel it once the rows cross >1k but is also affected by the number of columns since the total calls to adapter.convert_type() is n_rows * n_columns !

I hope this helps!

[benc-db]

Thanks for the pointers.

[nrichards17]

Hey @benc-db!

Looks like all of the important type definitions, inference, and overrides actually happen in the databricks__create_csv_table macro when the table is created (understandably), so there seems to be no need for casting types in the databricks__load_csv_rows macro.

I tested this out by pulling down the connector and removing the type inference code within the double loop in the databricks__load_csv_rows macro (see diff below):

           {% for row in chunk -%}
               ({%- for col_name in agate_table.column_names -%}
-                  {%- set inferred_type = adapter.convert_type(agate_table, loop.index0) -%}
-                  {%- set type = column_override.get(col_name, inferred_type) -%}
-                    cast({{ get_binding_char() }} as {{type}})
+                  {{ get_binding_char() }}
                   {%- if not loop.last%},{%- endif %}

I'm not aware of any reason why you would need to do CAST(variable) AS type on your INSERT statement after already defining your column type definitions during table creation, but I could be missing some important use case.

I was able to load a seed with 47k records and 9 columns in about 1 minute with this change, whereas previously the seed hadn't even finished after 10+ minutes of loading.

It's a simple enough change you can add & test for your next release, or I can submit a separate PR myself if you'd like. Thanks!

[benc-db]

@nrichards17 awesome; would very much appreciate a PR as I'm heads down on 1.7 compatibility at the moment, and then making MV/ST a real thing after that.