Supported SQL statements

Following SQL statements are supported in IBM® watsonx.data through different connectors.

watsonx.data on Red Hat OpenShift

IBM watsonx.data developer edition

Statements CREATE SCHEMA CREATE TABLE INSERT INTO SELECT SELECT (Complex) ALTER TABLE ALTER SCHEMA DELETE GRANT REVOKE SHOW GRANTS SHOW ROLES SHOW ROLE GRANTS UPDATE DROP TABLE CREATE ROLE CREATE VIEW DROP SCHEMA DROP VIEW
Iceberg ✓ ✓ ✓ ✓ ✓ ✓ X ✓ X X ✓ X X X ✓ X X ✓ X
MySQL X ✓ ✓ ✓ ✓ ✓ X X X X X X X X ✓ X X X X
PostgreSQL X ✓ ✓ ✓ ✓ ✓ X X X X X X X X ✓ X X X X
MongoDB X ✓ ✓ ✓ ✓ ✓ X X X X ✓ X X X ✓ X X X X
Hive ✓ ✓ ✓ ✓ ✓ X X ✓ ✓ ✓ X ✓ ✓ X ✓ ✓ ✓ ✓ ✓
Kafka X X X ✓ ✓ X X X X X ✓ X X X X X X X X
Memory ✓ ✓ ✓ ✓ ✓ X X X X X ✓ X X X ✓ X ✓ ✓ ✓
TPCH X X X ✓ ✓ X X X X X ✓ X X X X X X X X
TPCDS X X X ✓ ✓ X X X X X ✓ X X X X X X X X
System X X X ✓ ✓ X X X X X ✓ X X X X X X X X
JMX X X X ✓ ✓ X X X X X ✓ X X X X X X X X
Iceberg in AWS Glue ✓ X X ✓ ✓ X X X -- -- -- -- -- -- -- -- -- -- --
AWS Glue as Meta Store ✓ ✓ ✓ ✓ -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Db2 ✓ ✓ ✓ ✓ -- ✓ X X X X ✓ X X X ✓ X ✓ ✓ X
Netezza® Performance Server ✓ ✓ ✓ ✓ -- ✓ X X X X ✓ X X X ✓ X ✓ ✓ X
SQL Server X ✓ ✓ ✓ ✓ ✓ -- X X X ✓ X X X ✓ X X X X
SingleStore X ✓ ✓ ✓ ✓ ✓ X X X X ✓ X X X ✓ X X X X
Elasticsearch X X X ✓ ✓ X X X X X ✓ X X X X X X X X
Teradata X ✓ ✓ ✓ ✓ ✓ X X X X ✓ X X X ✓ X X X X
Snowflake X ✓ ✓ ✓ ✓ ✓ X X X X ✓ X X X ✓ X X X X
SAP HANA X ✓ ✓ ✓ ✓ X X X X X ✓ X X X ✓ X X X X
Delta Lake X ✓ X ✓ ✓ X X X X X X X X X ✓ X X X X

Limitations

  1. For CREATE TABLE, MySQL connector supports only CREATE TABLE AS.
  2. For CREATE TABLE, Snowflake connector also supports CREATE TABLE AS.
  3. For ALTER TABLE, MongoDB connector supports only TABLE RENAME.
  4. Db2 connector partially supports ALTER TABLE, CREATE VIEW, and DROP SCHEMA.
  5. Netezza Performance Server connector partially supports ALTER TABLE and CREATE VIEW.
  6. MySQL, PostgreSQL, MongoDB, Db2, Teradata, Snowflake, SAP HANA, SQL Serverand Netezza Performance Server connectors support DROP TABLE only when enabled in catalog.
  7. For the database-based catalogs, CREATE SCHEMA, CREATE TABLE, DROP SCHEMA, DROP TABLE, DELETE, DROP VIEW, ALTER TABLE, and ALTER SCHEMA are not available for database based catalogs in the Data Manager UI.
  8. For Db2, you can create the view for a table only if that table is in the same catalog and same schema.
  9. For Netezza Performance Server, you can create the view for a table only if that table is in the same catalog and same schema.