MySQL

Catalog Managed

Requirements

You should be:

🧑‍💻 A super user with the ability to run administrative commands

🕙 Your MySQL version should be ≥ 5.7

1. Whitelist Catalog's IP on MySQL

To allow Catalog to connect to MySQL, you will first need to whitelist Catalog's IP address:

Client whitelists Catalog's IP: 35.246.176.138

Catalog connects directly to your MySQL databases. As such, it needs to be public. It is frequent that our clients provides Catalog an access to an exact replica of their database.

2. Create a Catalog User on MySQL

Create a Catalog user

Use a safe password, from a password manager for example.

CREATE USER 'catalog'@'%' IDENTIFIED BY '<password>';

Grant rights to Catalog user

GRANT SELECT,SHOW VIEW,EXECUTE ON *.* TO 'catalog'@'%';

EXECUTE is only required for MySQL 5.7

3. Add new credentials in Catalog App

You must be a Catalog admin to do this.

You can now enter the newly created credentials in the Catalog App here.

• Go to "Settings > Integrations"

• Click on "MySQL Add"

• Add the credentials using the following format:

{
	"host": "host",
	"port": "port",
	"user": "user",
	"password": "password"
}

• host: MySQL Host

• port: MySQL Port

• user: MySQL User

• password: MySQL Password

Client Managed

Doing a one shot extract

For your trial, you can simply give us a one shot view of your MySQL metadata. In order to do this, you need to extract MySQL Metadata to this format and then upload files to Catalog.

To do so, you can use the following queries to extract the necessary informations for each files:

• database.csv

SELECT 
  CATALOG_NAME AS `id`,
  CATALOG_NAME AS `database_name`
FROM INFORMATION_SCHEMA.SCHEMATA
GROUP BY 1, 2

• schema.csv

SELECT 
  CONCAT(CATALOG_NAME, ".", SCHEMA_NAME) AS `id`,
  CATALOG_NAME AS `database_id`,
  SCHEMA_NAME AS `schema_name`,
  "" AS `description`,
  "" AS `tags`
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME NOT IN ("INFORMATION_SCHEMA")

• table.csv

SELECT 
  CONCAT(TABLE_CATALOG, ".", TABLE_SCHEMA, ".", TABLE_NAME) AS `id`,
  CONCAT(TABLE_CATALOG, ".", TABLE_SCHEMA) AS `schema_id`,
  TABLE_NAME AS `table_name`,
  TABLE_COMMENT AS `description`,
  "" AS `tags`,
  CASE 
    WHEN TABLE_TYPE = "BASE TABLE" THEN "TABLE"
    WHEN TABLE_TYPE = "SYSTEM VIEW" THEN "VIEW"
    ELSE TABLE_TYPE
  END AS `type`
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA NOT IN ("INFORMATION_SCHEMA")

• column.csv

SELECT 
  CONCAT(TABLE_CATALOG, ".", TABLE_SCHEMA, ".", TABLE_NAME, ".", COLUMN_NAME) AS `id`,
  CONCAT(TABLE_CATALOG, ".", TABLE_SCHEMA, ".", TABLE_NAME) AS `table_id`,
  COLUMN_NAME AS `column_name`,
  COLUMN_COMMENT AS `description`,
  CASE
    WHEN DATA_TYPE IN ("char", "varchar") THEN "STRING"
    WHEN DATA_TYPE IN ("binary", "varbinary", "blob", "tinyblob", "mediumblob", "longblob") THEN "BINARY"
    WHEN DATA_TYPE IN ("text", "tinytext", "mediumtext", "longtext") THEN "TEXT"
    WHEN DATA_TYPE IN ("enum", "set") THEN "ARRAY"
    WHEN DATA_TYPE IN ("bit", "tinyint", "smallint", "mediumint", "int", "bigint", "integer", "long") THEN "INTEGER"
    WHEN DATA_TYPE IN ("bool") THEN "BOOLEAN"
    WHEN DATA_TYPE IN ("float", "real") THEN "FLOAT"
    WHEN DATA_TYPE IN ("double", "double precision", "decimal", "dec", "numeric", "fixed") THEN "DECIMAL"
    WHEN DATA_TYPE IN ("date", "datetime", "year") THEN "DATE"
    WHEN DATA_TYPE IN ("timestamp") THEN "TIMESTAMP"
    WHEN DATA_TYPE IN ("time") THEN "TIME"
    WHEN DATA_TYPE IN ("json") THEN "OBJECT"
    ELSE UPPER(DATA_TYPE)
  END AS `data_type`,
  ORDINAL_POSITION AS `ordinal_position`
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA NOT IN ("INFORMATION_SCHEMA")

• query.csv

Please use the following file - we currently can't retrieve your queries for this integration

85B

query.csv

• view_ddl.csv

SELECT 
  TABLE_CATALOG AS `database_name`,
  TABLE_SCHEMA AS `schema_name`,
  TABLE_NAME AS `view_name`,
  VIEW_DEFINITION AS `definition`
FROM INFORMATION_SCHEMA.VIEWS

• user.csv

For MySQL 8.0 and higher

SELECT
  USER AS `id`,
  CONCAT(USER, "@", HOST) AS `email`
FROM INFORMATION_SCHEMA.USER_ATTRIBUTES
GROUP BY 1, 2

For other versions of MySQL

SELECT
  GRANTEE AS `id`,
  GRANTEE AS `email`
FROM INFORMATION_SCHEMA.USER_PRIVILEGES
GROUP BY 1, 2

You can upload the resulting files into the App directly.

After your trial

You will need to generate the 7 files above and push them to our endpoint using the Catalog Uploader

We will provide you with an API secret and a source_id

All 7 files are mandatory and data must be consistent and coherent