Skip to content

Latest commit

 

History

History
245 lines (191 loc) · 9.57 KB

jdbc-mysql-catalog.md

File metadata and controls

245 lines (191 loc) · 9.57 KB
title slug keywords license
MySQL catalog
/jdbc-mysql-catalog
jdbc
MySQL
metadata
This software is licensed under the Apache License version 2.

import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';

Introduction

Apache Gravitino provides the ability to manage MySQL metadata.

:::caution Gravitino saves some system information in schema and table comment, like (From Gravitino, DO NOT EDIT: gravitino.v1.uid1078334182909406185), please don't change or remove this message. :::

Catalog

Catalog capabilities

  • Gravitino catalog corresponds to the MySQL instance.
  • Supports metadata management of MySQL (5.7, 8.0).
  • Supports DDL operation for MySQL databases and tables.
  • Supports table index.
  • Supports column default value and auto-increment.
  • Supports managing MySQL table features though table properties, like using engine to set MySQL storage engine.

Catalog properties

You can pass to a MySQL data source any property that isn't defined by Gravitino by adding gravitino.bypass. prefix as a catalog property. For example, catalog property gravitino.bypass.maxWaitMillis will pass maxWaitMillis to the data source property.

Check the relevant data source configuration in data source properties

When you use the Gravitino with Trino. You can pass the Trino MySQL connector configuration using prefix trino.bypass.. For example, using trino.bypass.join-pushdown.strategy to pass the join-pushdown.strategy to the Gravitino MySQL catalog in Trino runtime.

If you use a JDBC catalog, you must provide jdbc-url, jdbc-driver, jdbc-user and jdbc-password to catalog properties. Besides the common catalog properties, the MySQL catalog has the following properties:

Configuration item Description Default value Required Since Version
jdbc-url JDBC URL for connecting to the database. For example, jdbc:mysql://localhost:3306 (none) Yes 0.3.0
jdbc-driver The driver of the JDBC connection. For example, com.mysql.jdbc.Driver or com.mysql.cj.jdbc.Driver. (none) Yes 0.3.0
jdbc-user The JDBC user name. (none) Yes 0.3.0
jdbc-password The JDBC password. (none) Yes 0.3.0
jdbc.pool.min-size The minimum number of connections in the pool. 2 by default. 2 No 0.3.0
jdbc.pool.max-size The maximum number of connections in the pool. 10 by default. 10 No 0.3.0

:::caution You must download the corresponding JDBC driver to the catalogs/jdbc-mysql/libs directory. :::

Catalog operations

Refer to Manage Relational Metadata Using Gravitino for more details.

Schema

Schema capabilities

  • Gravitino's schema concept corresponds to the MySQL database.
  • Supports creating schema, but does not support setting comment.
  • Supports dropping schema.
  • Supports cascade dropping schema.

Schema properties

  • Doesn't support any schema property settings.

Schema operations

Refer to Manage Relational Metadata Using Gravitino for more details.

Table

Table capabilities

  • Gravitino's table concept corresponds to the MySQL table.
  • Supports DDL operation for MySQL tables.
  • Supports index.
  • Supports column default value and auto-increment..
  • Supports managing MySQL table features though table properties, like using engine to set MySQL storage engine.

Table column types

Gravitino Type MySQL Type
Byte Tinyint
Byte(false) Tinyint Unsigned
Short Smallint
Short(false) Smallint Unsigned
Integer Int
Integer(false) Int Unsigned
Long Bigint
Long(false) Bigint Unsigned
Float Float
Double Double
String Text
Date Date
Time Time
Timestamp Timestamp
Decimal Decimal
VarChar VarChar
FixedChar FixedChar
Binary Binary

:::info MySQL doesn't support Gravitino Boolean Fixed Struct List Map Timestamp_tz IntervalDay IntervalYear Union UUID type. Meanwhile, the data types other than listed above are mapped to Gravitino External Type that represents an unresolvable data type since 0.6.0. :::

Table column auto-increment

:::note MySQL setting an auto-increment column requires simultaneously setting a unique index; otherwise, an error will occur. :::

{
  "columns": [
    {
      "name": "id",
      "type": "integer",
      "comment": "id column comment",
      "nullable": false,
      "autoIncrement": true
    },
    {
      "name": "name",
      "type": "varchar(500)",
      "comment": "name column comment",
      "nullable": true,
      "autoIncrement": false
    }
  ],
  "indexes": [
    {
      "indexType": "primary_key",
      "name": "PRIMARY",
      "fieldNames": [["id"]]
    }
  ]
}
Column[] cols = new Column[] {
    Column.of("id", Types.IntegerType.get(), "id column comment", false, true, null),
    Column.of("name", Types.VarCharType.of(500), "Name of the user", true, false, null)
};
Index[] indexes = new Index[] {
    Indexes.of(IndexType.PRIMARY_KEY, "PRIMARY", new String[][]{{"id"}})
}

Table properties

Although MySQL itself does not support table properties, Gravitino offers table property management for MySQL tables through the jdbc-mysql catalog, enabling control over table features. The supported properties are listed as follows:

Property Name Description Required Since version
engine The engine used by the table. The default value is InnoDB. For example MyISAM, MEMORY, CSV, ARCHIVE, BLACKHOLE, FEDERATED, ndbinfo, MRG_MYISAM, PERFORMANCE_SCHEMA. No 0.4.0
auto-increment-offset Used to specify the starting value of the auto-increment field. No 0.4.0
  • Doesn't support remove table properties. You can only modify values, not delete properties.

Table indexes

  • Supports PRIMARY_KEY and UNIQUE_KEY.

:::note The index name of the PRIMARY_KEY must be PRIMARY Create table index :::

{
  "indexes": [
    {
      "indexType": "primary_key",
      "name": "PRIMARY",
      "fieldNames": [["id"]]
    },
    {
      "indexType": "unique_key",
      "name": "id_name_uk",
      "fieldNames": [["id"] ,["name"]]
    }
  ]
}
Index[] indexes = new Index[] {
    Indexes.of(IndexType.PRIMARY_KEY, "PRIMARY", new String[][]{{"id"}}),
    Indexes.of(IndexType.UNIQUE_KEY, "id_name_uk", new String[][]{{"id"} , {"name"}}),
}

Table operations

Refer to Manage Relational Metadata Using Gravitino for more details.

Alter table operations

Gravitino supports these table alteration operations:

  • RenameTable
  • UpdateComment
  • AddColumn
  • DeleteColumn
  • RenameColumn
  • UpdateColumnType
  • UpdateColumnPosition
  • UpdateColumnNullability
  • UpdateColumnComment
  • UpdateColumnDefaultValue
  • SetProperty

:::info

  • You cannot submit the RenameTable operation at the same time as other operations.
  • If you update a nullability column to non-nullability, there may be compatibility issues. :::