title | slug | keywords | license | |||
---|---|---|---|---|---|---|
MySQL catalog |
/jdbc-mysql-catalog |
|
Copyright 2023 Datastrato Pvt Ltd. This software is licensed under the Apache License version 2. |
import Tabs from '@theme/Tabs'; import TabItem from '@theme/TabItem';
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.
:::
- 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.
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
If you use a JDBC catalog, you must provide jdbc-url
, jdbc-driver
, jdbc-user
and jdbc-password
to catalog 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.
:::
Refer to Manage Relational Metadata Using Gravitino for more details.
- Gravitino's schema concept corresponds to the MySQL database.
- Supports creating schema, but does not support setting comment.
- Supports dropping schema.
- Doesn't support cascade dropping schema.
- Doesn't support any schema property settings.
Refer to Manage Relational Metadata Using Gravitino for more details.
- 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.
Gravitino Type | MySQL Type |
---|---|
Byte |
Tinyint |
Short |
Smallint |
Integer |
Int |
Long |
Bigint |
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 Unparsed Type that represents an unresolvable data type since 0.5.0.
:::
:::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"}})
}
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.
- 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"}}),
}
Refer to Manage Relational Metadata Using Gravitino for more details.
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. :::