3 posts tagged with "SQL"

The v0.13.0-alpha release significantly improves federated query performance and efficiency with Query Push-Down. Query push-down allows SQL queries to be directly executed by underlying data sources, such as joining tables using the same data connector. Query push-down is supported for all SQL-based and Arrow Flight data connectors. Additionally, runtime metrics, including query duration, collected and accessed in the spice.runtime.metrics table. This release also includes a new FTP/SFTP data connector and improved CSV support for the S3 data connector.

Highlights​

• Federated Query Push-Down (#1394): All SQL and Arrow Flight data connectors support federated query push-down.

• Runtime Metrics (#1361): Runtime metric collection can be enabled using the --metrics flag and accessed by the spice.runtime.metrics table.

• FTP & SFTP data connector (#1355) (#1399): Added support for using FTP and SFTP as data sources.

• Improved CSV support (#1411) (#1414): S3/FTP/SFTP data connectors support CSV files with expanded CSV options.

Contributors​

• @Jeadie
• @digadeesh
• @ewgenius
• @gloomweaver
• @lukekim
• @phillipleblanc
• @sgrebnov
• @y-f-u

What's Changed​

• Remove milestones from Enhancement template by @lukekim in https://github.com/spiceai/spiceai/pull/1373
• Update version.txt and Cargo.toml to 0.13.0-alpha by @sgrebnov in https://github.com/spiceai/spiceai/pull/1375
• Helm chart for Spice v0.12.2-alpha by @sgrebnov in https://github.com/spiceai/spiceai/pull/1374
• Add release cargo feature to docker builds by @ewgenius in https://github.com/spiceai/spiceai/pull/1377
• FTP connector by @gloomweaver in https://github.com/spiceai/spiceai/pull/1355
• Provide ability to specify timeout for s3 data connector by @gloomweaver in https://github.com/spiceai/spiceai/pull/1378
• clickhouse-rs use tag instead of branch by @gloomweaver in https://github.com/spiceai/spiceai/pull/1313
• Store runtime metrics in spice.runtime.metrics table by @ewgenius in https://github.com/spiceai/spiceai/pull/1361
• Update bug_report.md to include the kind/bug label by @digadeesh in https://github.com/spiceai/spiceai/pull/1381
• Remove redundant [refresh] in log by @lukekim in https://github.com/spiceai/spiceai/pull/1384
• Implement federation for DuckDB Data Connector (POC) by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1380
• Update wording for spice cloud connection by @ewgenius in https://github.com/spiceai/spiceai/pull/1386
• fix dataset refreshing status by @y-f-u in https://github.com/spiceai/spiceai/pull/1387
• clickhouse friendly error by @y-f-u in https://github.com/spiceai/spiceai/pull/1388
• Initial work for NQL crate and API by @Jeadie in https://github.com/spiceai/spiceai/pull/1366
• Fully implement federation for all SqlTable-based Data Connectors by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1394
• use df logical plan to query latest timestamp when refreshing incrementally by @y-f-u in https://github.com/spiceai/spiceai/pull/1393
• Refactor datafusion.write_data to use table reference by @ewgenius in https://github.com/spiceai/spiceai/pull/1402
• Add federation to FlightTable based DataConnectors by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1401
• SFTP Data Connector by @gloomweaver in https://github.com/spiceai/spiceai/pull/1399
• Use GPT3.5 for NSQL task by @Jeadie in https://github.com/spiceai/spiceai/pull/1400
• Update ROADMAP May 16, 2024 by @lukekim in https://github.com/spiceai/spiceai/pull/1405
• Add ftp/sftp connector to readme by @gloomweaver in https://github.com/spiceai/spiceai/pull/1404
• Add FlightSQL federation provider by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1403
• Refactor runtime metrics to use localhost accelerated table by @ewgenius in https://github.com/spiceai/spiceai/pull/1395
• Use JSON response in OpenAI, text -> SQL model by @Jeadie in https://github.com/spiceai/spiceai/pull/1407
• support more common csv options by @y-f-u in https://github.com/spiceai/spiceai/pull/1411
• add a TLS error message in data connector and implement it for clickhouse by @y-f-u in https://github.com/spiceai/spiceai/pull/1413
• Add CSV to s3 data formats by @gloomweaver in https://github.com/spiceai/spiceai/pull/1414
• fix up dependencies now 0.5.0 disappeared by @Jeadie in https://github.com/spiceai/spiceai/pull/1417
• Add NSQL to FlightRepl by @Jeadie in https://github.com/spiceai/spiceai/pull/1409
• Update Cargo.lock by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1418
• Enable spice.ai replication for runtime.metrics table by @ewgenius in https://github.com/spiceai/spiceai/pull/1408
• Restructure the runtime struct to make it easier to test by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1420
• Make it easier to construct an App programatically by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1421
• Add an integration test for federation by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1426
• wait 2 seconds for the status to turn ready in refreshing status test by @y-f-u in https://github.com/spiceai/spiceai/pull/1419
• Add functional tests for federation push-down by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1428
• Enable push-down federation by default by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1429
• Add guides and examples about error handling by @ewgenius in https://github.com/spiceai/spiceai/pull/1427
• Add LRU cache support for http-based queries by @sgrebnov in https://github.com/spiceai/spiceai/pull/1410
• Update README.md - Remove bigquery from tablet of connectors by @digadeesh in https://github.com/spiceai/spiceai/pull/1434
• Update acknowledgements by @github-actions in https://github.com/spiceai/spiceai/pull/1433
• CLI wording and logs change reflected on readme by @y-f-u in https://github.com/spiceai/spiceai/pull/1435
• Add databricks_use_ssl parameter by @Sevenannn in https://github.com/spiceai/spiceai/pull/1406
• Update helm version and use v0.13.0-alpha by @Jeadie in https://github.com/spiceai/spiceai/pull/1436
• Don't include feature 'llms/candles' by default by @Jeadie in https://github.com/spiceai/spiceai/pull/1437
• Correctly map NullBuilder for Null arrow types by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1438
• Propagate object store error by @gloomweaver in https://github.com/spiceai/spiceai/pull/1415

Full Changelog: https://github.com/spiceai/spiceai/compare/v0.12.2-alpha...v0.13.0-alpha

Resources​

• Getting started with Spice.ai
• Documentation

Community​

Spice.ai started with the vision to make AI easy for developers. We are building Spice.ai in the open and with the community. Reach out on Discord or by email to get involved.

• Twitter: @spice_ai
• Discord: https://discord.gg/kZnTfneP5u
• Telegram: Spice AI Discussion
• Reddit: https://www.reddit.com/r/spiceai
• Email: [email protected]

The v0.12.1-alpha release introduces a new Snowflake data connector, support for UUID and TimestampTZ types in the PostgreSQL connector, and improved error messages across all data connectors. The Clickhouse data connector enables data streaming by default. The public SQL interface now restricts DML and DDL queries. Additionally, accelerated tables now fully support NULL values, and issues with schema conversion in these tables have been resolved.

Highlights​

• Snowflake Data Connector: Initial support for Snowflake as a data source.

• Clickhouse Data Streaming: Enables data streaming by default, eliminating in-memory result collection.

• Read-only SQL Interface: Disables DML (INSERT/UPDATE/DELETE) and DDL (CREATE/ALTER TABLE) queries for improved data source security.

• Error Message Improvements: Improved the error messages for commonly encountered issues with data connectors.

• Accelerated Tables: Supports NULL values across all data types and fixes schema conversion errors for consistent type handling.

Contributors​

• @ahirner
• @y-f-u
• @sgrebnov
• @ewgenius
• @Jeadie
• @gloomweaver
• @Sevenannn
• @digadeesh
• @phillipleblanc

What's Changed​

• Add schema types check for query result by @sgrebnov in https://github.com/spiceai/spiceai/pull/1212
• helm chart for v0.12.0-alpha by @y-f-u in https://github.com/spiceai/spiceai/pull/1235
• Update acknowledgements by @github-actions in https://github.com/spiceai/spiceai/pull/1232
• Bump spiceai version to v0.12.1-alpha by @ewgenius in https://github.com/spiceai/spiceai/pull/1239
• Update ROADMAP.md - remove v0.12.0-alpha by @ewgenius in https://github.com/spiceai/spiceai/pull/1241
• Raise errors in InsertBuilder by @Jeadie in https://github.com/spiceai/spiceai/pull/1242
• Update endgame template by @ewgenius in https://github.com/spiceai/spiceai/pull/1240
• Add E2E tests for acceleration engines types support by @sgrebnov in https://github.com/spiceai/spiceai/pull/1218
• Stream blocks to arrow by @gloomweaver in https://github.com/spiceai/spiceai/pull/1203
• Update enhancement.md to include a checklist item have a release notes entry for each enhancement. by @digadeesh in https://github.com/spiceai/spiceai/pull/1245
• arrow_sql_gen data column conversion by @Sevenannn in https://github.com/spiceai/spiceai/pull/1230
• Implement the Localhost Data Connector & fix DoPut by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1266
• Update postgres parameter check by @Sevenannn in https://github.com/spiceai/spiceai/pull/1244
• Record batch casting to fix SQLite data type issues by @y-f-u in https://github.com/spiceai/spiceai/pull/1261
• typo fix on Decimal in postgres arrow_sql_gen by @y-f-u in https://github.com/spiceai/spiceai/pull/1277
• Move verify_schema to arrow_tools by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1284
• Support UUID and TimestampTZ type for Postgres as Data Connector by @ahirner & @y-f-u https://github.com/spiceai/spiceai/pull/1276
• Fix linter warnings by @ewgenius in https://github.com/spiceai/spiceai/pull/1286
• Add Snowflake data connector by @sgrebnov in https://github.com/spiceai/spiceai/pull/1278
• Add Snowflake login support (username and password) by @sgrebnov in https://github.com/spiceai/spiceai/pull/1272
• convert timestamp properly in sql gen by @y-f-u in https://github.com/spiceai/spiceai/pull/1291
• Add if not exists clause to create statement on when creating a table using duckdb acceleration. by @digadeesh in https://github.com/spiceai/spiceai/pull/1290
• Disable DML & DDL queries in the public SQL interface by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1294
• Refactor duckdb to properly set access_mode for connection by @ewgenius in https://github.com/spiceai/spiceai/pull/1285
• do not insert batch for sqlite and postgres if no records in the record batch by @y-f-u in https://github.com/spiceai/spiceai/pull/1293
• Postgres - add custom error message for invalid error table by @ewgenius in https://github.com/spiceai/spiceai/pull/1295
• SQLite/Accelerators handle null values by @gloomweaver in https://github.com/spiceai/spiceai/pull/1298
• Add command to attach to running process by @gloomweaver in https://github.com/spiceai/spiceai/pull/1297
• Use the GITHUB_TOKEN environment variable in the installation script, if available, to avoid rate limiting in CI workflows by @ewgenius in https://github.com/spiceai/spiceai/pull/1302
• Fix unsupported SSL mode options for PostgreSQL connection string by @ewgenius in https://github.com/spiceai/spiceai/pull/1300
• Add CLI cmd spice login spark by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1303
• Check only the latest published release to avoid installing pre-release versions by @ewgenius in https://github.com/spiceai/spiceai/pull/1301
• Postgres data connector - handle invalid host/port and username/password errors by @ewgenius in https://github.com/spiceai/spiceai/pull/1292
• Fix the panic on bad clickhouse connection by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1306
• Improve Snowflake Data Connector by @sgrebnov https://github.com/spiceai/spiceai/pull/1296

Full Changelog: https://github.com/spiceai/spiceai/compare/v0.12.0-alpha...v0.12.1-alpha

Resources​

• Getting started with Spice.ai
• Documentation

Community​

Spice.ai started with the vision to make AI easy for developers. We are building Spice.ai in the open and with the community. Reach out on Discord or by email to get involved.

• Twitter: @spice_ai
• Discord: https://discord.gg/kZnTfneP5u
• Telegram: Spice AI Discussion
• Reddit: https://www.reddit.com/r/spiceai
• Email: [email protected]

The Spice v0.11-alpha release significantly improves the Databricks data connector with Databricks Connect (Spark Connect) support, adds the DuckDB data connector, and adds the AWS Secrets Manager secret store. In addition, enhanced control over accelerated dataset refreshes, improved SSL security for MySQL and PostgreSQL connections, and overall stability improvements have been added.

Highlights in v0.11-alpha​

DuckDB data connector: Use DuckDB databases or connections as a data source.

AWS Secrets Manager Secret Store: Use AWS Secrets Managers as a secret store.

Custom Refresh SQL: Specify a custom SQL query for dataset refresh using refresh_sql.

Dataset Refresh API: Trigger a dataset refresh using the new CLI command spice refresh or via API.

Expanded SSL support for Postgres: SSL mode now supports disable, require, prefer, verify-ca, verify-full options with the default mode changed to require. Added pg_sslrootcert parameter for setting a custom root certificate and the pg_insecure parameter is no longer supported.

Databricks Connect: Choose between using Spark Connect or Delta Lake when using the Databricks data connector for improved performance.

Improved SSL support for Postgres: ssl mode now supports disable, require, prefer, verify-ca, verify-full options with default mode changed to require. Added pg_sslrootcert parameter to allow setting custom root cert for postgres connector, pg_insecure parameter is no longer supported as redundant.

Internal architecture refactor: The internal architecture of spiced was refactored to simplify the creation data components and to improve alignment with DataFusion concepts.

New Contributors​

@edmondop's first contribution github.com/spiceai/spiceai/pull/1110!

Contributors​

• @phillipleblanc
• @Jeadie
• @ewgenius
• @sgrebnov
• @y-f-u
• @lukekim
• @digadeesh
• @Sevenannn
• @gloomweaver
• @ahirner

New in this release​

• Fixes MySQL NULL values by @gloomweaver in https://github.com/spiceai/spiceai/pull/1067
• Fixes PostgreSQL NULL values for NUMERIC by @gloomweaver in https://github.com/spiceai/spiceai/pull/1068
• Adds Custom Refresh SQL support by @lukekim and @phillipleblanc in https://github.com/spiceai/spiceai/pull/1073
• Adds DuckDB data connector by @Sevenannn in https://github.com/spiceai/spiceai/pull/1085
• Adds AWS Secrets Manager secret store by @sgrebnov in https://github.com/spiceai/spiceai/pull/1063, https://github.com/spiceai/spiceai/pull/1064
• Adds Dataset refresh API by @sgrebnov in https://github.com/spiceai/spiceai/pull/1075, https://github.com/spiceai/spiceai/pull/1078, https://github.com/spiceai/spiceai/pull/1083
• Adds spice refresh CLI command for dataset refresh by @sgrebnov in https://github.com/spiceai/spiceai/pull/1112
• Adds TEXT and DECIMAL types support and properly handling NULL for MySQL by @gloomweaver in https://github.com/spiceai/spiceai/pull/1067
• Adds MySQL DATE and TINYINT types support for MySQL by @ewgenius in https://github.com/spiceai/spiceai/pull/1065
• Adds ssl_rootcert_path parameter for MySql data connector by @ewgenius in https://github.com/spiceai/spiceai/pull/1079
• Adds LargeUtf8 support and explicitly passing the schema to data accelerator SqlTable by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1077
• Adds Ability to configure data retention for accelerated datasets by @y-f-u in https://github.com/spiceai/spiceai/issues/1086
• Adds Custom SSL certificates for PostgreSQL data connector by @ewgenius in https://github.com/spiceai/spiceai/pull/1081
• Adds Conditional compile for Dremio by @ahirner in https://github.com/spiceai/spiceai/pull/1100
• Adds Ability for Databricks connector to use spark-connect-rs as the mechanism to execute queries against the Databricks by @edmondop in https://github.com/spiceai/spiceai/pull/1110
• Adds Ability to choose between Spark Connect and Delta Lake implementation for Databricks by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1115/files
• Updates Databricks login parameters by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1113
• Updates Architecture to simplify data components development by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1040
• Updates Improved readability of GitHub Actions test job names by @lukekim in https://github.com/spiceai/spiceai/pull/1071
• Updates Upgrade Arrow, DataFusion, Tonic dependencies by @phillipleblanc in https://github.com/spiceai/spiceai/pull/1097
• Updates Handling non-string spicepod params by @ewgenius in https://github.com/spiceai/spiceai/pull/1098
• Updates Optional features compile: duckdb, databricks by @ahirner in https://github.com/spiceai/spiceai/pull/1100
• Updates Helm version to 0.1.3 by @Jeadie in https://github.com/spiceai/spiceai/pull/1120
• Removes pg_insecure parameter support from Postgres by ewgenius in https://github.com/spiceai/spiceai/pull/1081

Full Changelog: https://github.com/spiceai/spiceai/compare/v0.10.2-alpha...v0.11.0-alpha

Resources​

• Getting started with Spice.ai
• Documentation

Community​

Spice.ai started with the vision to make AI easy for developers. We are building Spice.ai in the open and with the community. Reach out on Discord or by email to get involved.

• Twitter: @spice_ai
• Discord: https://discord.gg/kZnTfneP5u
• Telegram: Spice AI Discussion
• Reddit: https://www.reddit.com/r/spiceai
• Email: [email protected]