You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@arrow.apache.org by "davlee1972 (via GitHub)" <gi...@apache.org> on 2023/11/22 21:11:28 UTC
[I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
davlee1972 opened a new issue, #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322
Problem: Bulk inserting a pyarrow table using adbc_ingest creates a bind array insert operation for every record batch. It takes 3 hours to insert 36 million rows across 3,000 record batches created by the pyarrow multithreaded CSV reader using adbc_ingest().
If you reorg the data into 36 record batches of 1 million rows each the same adbc_ingest() call only takes 3 minutes..
Sample code:
```
new_recordbatch = {column: [] for column in my_data.schema.names}
new_batches = []
rows = 0
for batch in my_data.to_batches():
for column in my_data.schema.names:
new_recordbatch[column].append(batch[column])
if rows < 1000000:
rows += batch.num_rows
else:
print(rows)
new_arrays = []
for column in my_data.schema.names:
new_arrays.append(pa.concat_arrays(new_recordbatch[column]))
new_batches.append(pa.RecordBatch.from_arrays(new_arrays, schema=my_data.schema))
new_recordbatch = {column: [] for column in my_data.schema.names}
rows = 0
if rows > 0:
new_arrays = []
for column in my_data.schema.names:
new_arrays.append(pa.concat_arrays(new_recordbatch[column]))
new_batches.append(pa.RecordBatch.from_arrays(new_arrays, schema=my_data.schema))
new_table = pa.Table.from_batches(new_batches)
cursor = adbc_conn.cursor()
cursor.adbc_ingest(table_name="xyz", data=new_table, mode="append")
cursor.execute("commit")
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "joellubi (via GitHub)" <gi...@apache.org>.
joellubi commented on issue #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322#issuecomment-1840565785
Got it. So it does appear likely that your arrow table meets the Snowflake Connectors conditions linked above to be optimized for bulk ingestion. For context, if those conditions are not met then an even slower approach will be taken which renders all the values into the `INSERT` query itself.
Most likely in your case the connector is taking a faster approach of uploading the arrow records to a temporary stage and then inserting from there. In part, certain limitations on datatypes are imposed because CSV is always used for the format of the temporary stage and all types are first converted to builtin go types. Since you are using CSV as input, this doesn't appear to be an issue.
Given that this code is already making use of the Snowflake Connector's optimized ingestion path but still experiencing limitations, I do think it makes sense to handle this on the ADBC side rather than delegate to the connector. I'm bringing some follow-up discussion on potential solutions to #1327.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "davlee1972 (via GitHub)" <gi...@apache.org>.
davlee1972 closed issue #1322: Improve Snowflake bind array inserts operations using adbc_ingest()
URL: https://github.com/apache/arrow-adbc/issues/1322
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: issues-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "davlee1972 (via GitHub)" <gi...@apache.org>.
davlee1972 commented on issue #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322#issuecomment-1960557476
@joellubi - I just installed and tested it..
For 100 million records x 8 columns across 1,000 CSV files..
0.9.0 took 5.5 hours.
0.9.0 took 17 minutes with my custom 10 million row record batching.
0.10.0 took 2 minutes using default settings without any custom record batching..
Thanks.. Marking this closed..
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "davlee1972 (via GitHub)" <gi...@apache.org>.
davlee1972 commented on issue #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322#issuecomment-1839166597
The original source are csv files and every column in the schema are just string types..
I can confirm that when reading the csv files using multithreaded arrow you end up with a pyarrow table / recordbatch reader that produces record batch lengths of 12k, 13k, 114k, etc..
The adbc ingest function just happens to send multiple snowflake insert ? ? ? ? array inserts that match 12k, 13k, 114k, etc rows inserted in the history log.
By merging multiple record batch arrays into lengths of 1 million before calling adbc ingest I can see 1 million row bind inserts in Snowflake.
Each bind insert is taking 5 seconds so 36 bind inserts takes 3 minutes..
Before with 3000 record batches the same bind insert was taking 3 hours..
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "joellubi (via GitHub)" <gi...@apache.org>.
joellubi commented on issue #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322#issuecomment-1954610805
@davlee1972 The PR (#1456) including this improvement merged last month and is slated for the upcoming 0.10.0 release. The details are available on the current [dev docs build](https://arrow.apache.org/adbc/main/driver/snowflake.html#bulk-ingestion).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "davlee1972 (via GitHub)" <gi...@apache.org>.
davlee1972 commented on issue #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322#issuecomment-1823511791
Forgot to add
adbc_ingest() needs something similar to https://arrow.apache.org/docs/python/generated/pyarrow.dataset.write_dataset.html which supports max_partitions=None, max_open_files=None, max_rows_per_file=None, min_rows_per_group=None, max_rows_per_group=None,
to control/optimized bulk insert throughput..
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Improve Snowflake bind array inserts operations using adbc_ingest() [arrow-adbc]
Posted by "joellubi (via GitHub)" <gi...@apache.org>.
joellubi commented on issue #1322:
URL: https://github.com/apache/arrow-adbc/issues/1322#issuecomment-1838556402
@davlee1972 Can you please share the arrow schema of the table you ingested in this example? I ask because the underlying snowflake client makes certain optimizations with the stream that is handed to it, but the behavior is currently limited to only [certain datatypes](https://github.com/snowflakedb/gosnowflake/blob/master/bind_uploader.go#L280).
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: github-unsubscribe@arrow.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org