Create a MariaDB Data Source

Follow these steps to connect to MariaDB and create a MariaDB data source:

  1. Configure User Access Rights

  2. Create a MariaDB Data Source

Refer to Additional Information for additional performance tips and information about MariaDB feature support.

Configure User Access Rights

We recommend that you create a dedicated user and user role for integration with the GoodData platform.

Steps:

  1. Create a user role and grant access rights:

    CREATE ROLE {role_name};
GRANT SELECT ON {database_name}.* TO {role_name};

  2. Create a user and grant it the user role:

    CREATE USER {user_name};
GRANT {role_name} TO {user_name};

  3. Make the user role default for the user:

    ALTER USER {user_name} DEFAULT ROLE {role_name};

Create a MariaDB Data Source

Once you have configured your MariaDB user’s access rights, you can proceed to create a MariaDB data source that you can then connect to.

UI
API

Steps:

  1. On the home page switch to Data sources.

    data sources tab

  2. Click Connect data.

    connect data

  3. Select MariaDB.

    select data source type

  4. Name your data source and fill in your MariaDB credentials and click Connect:

    db cretentials

  5. Click Save.

    Your data source is created!

    db creation complete

Steps:

  1. Create a MariaDB data source with the following API call:

    curl $HOST_URL/api/v1/entities/dataSources \
-H "Content-Type: application/vnd.gooddata.api+json" \
-H "Accept: application/vnd.gooddata.api+json" \
-H "Authorization: Bearer $API_TOKEN" \
-X POST \
-d '{
    "data": {
    "type": "dataSource",
    "id": "<unique_id_for_the_data_source>",
    "attributes": {
    "name": "<data_source_display_name>",
    "url": "jdbc:mariadb:https://<MARIADB_HOST>:3306/<MARIADB_DBNAME>",
    "schema": "<MARIADB_DBNAME>",
    "type": "MARIADB",
    "username": "<MARIADB_USER>",
    "password": "<MARIADB_PASSWORD>"
}}}' | jq .

  2. To confirm that the data source has been created, ensure the server returns the following response:

    {
  "data": {
    "type": "dataSource",
    "id": "<unique_id_for_the_data_source>",
    "attributes": {
      "name": "<data_source_display_name>",
      "url": "jdbc:mariadb:https://<MARIADB_HOST>:3306/<MARIADB_DBNAME>",
      "schema": "<MARIADB_DBNAME>",
      "type": "MARIADB",
      "username": "<MARIADB_USER>"
    }
  },
  "links": {
    "self": "$HOST_URL/api/v1/entities/dataSources/<unique_id_for_the_data_source>"
  }
}

Additional Information

Ensure you understand the following limitations and recommended practice.

Data Source Details

  • Typical JDBC URL may look like this:

    jdbc:mariadb:https://<host>:<port>/<databaseName>

    For secured connection using SSL include ?sslMode=verify-full.

  • Basic authentication is supported. Specify user and password.

  • GoodData uses up-to-date drivers.

Unsupported Features and Beta Limitations

GoodData does not support the following features:

  • There are known issues when using SQL datasets with a MariaDB database:
    • Converting a regular dataset into a SQL dataset generates an invalid query. You have to manually rewrite it to make it work.
    • SQL datasets are not supported if the query contains one of these data types: TINYINT, FLOAT, DECIMAL, DOUBLE UNSIGNED.
    • When converting to SQL dataset, the MySQL YEAR data type is converted to STRING.
  • Filtering by boolean columns is not supported.
  • The following functions are not supported:
    • CORREL
    • COVAR
    • RSQ
    • SLOPE
    • INTERCEPT
  • LAST_VALUE, FIRST_VALUE may use only one fact/attribute in the ORDER BY clause, string attributes are not supported.
  • We do not currently support multiple hosts for MariaDB JDBC URLs.
MySQL
Oracle Database