Create a SingleStore Data Source

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

  1. Configure User Access Rights

  2. Create a SingleStore Data Source

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

Configure User Access Rights

We recommend creating 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 SingleStore Data Source

Once you have configured your SingleStore user’s access rights, you can create a SingleStore data source to which you can connect.

UI
API

Steps:

  1. On the home page, switch to Data sources.

    data sources tab

  2. Click Connect data.

    connect data

  3. Select SingleStore.

    select data source type

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

    db credentials

  5. Click Save.

    Your data source is created!

Steps:

  1. Create a SingleStore 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:mysql:https://<SINGLESTORE_HOST>:3306/<SINGLESTORE_DBNAME>",
    "schema": "<SINGLESTORE_DBNAME>",
    "type": "SINGLESTORE",
    "username": "<SINGLESTORE_USER>",
    "password": "<SINGLESTORE_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:mysql:https://<SINGLESTORE_HOST>:3306/<SINGLESTORE_DBNAME>",
      "schema": "<SINGLESTORE_DBNAME>",
      "type": "SINGLESTORE",
      "username": "<SINGLESTORE_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

  • A typical JDBC URL may look like this:

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

    For secured connection using SSL, include ?sslMode=required.

    We append the permitMysqlScheme=true parameter to all SingleStore JDBC URL calls.

  • Basic authentication is supported. Specify user and password.

  • Permissions for Data Rendering:

    • GRANT SELECT permission is sufficient for rendering data.
    • If caching is desired, CREATE and DROP permissions are necessary for the caching database.
  • GoodData uses up-to-date drivers.

Unsupported Features and Limitations

GoodData does not currently support the following features:

  • The following functions are not supported:
    • CORREL
    • COVAR
    • RSQ
    • SLOPE
    • INTERCEPT
  • Filtering by boolean columns is not supported
  • There are known issues when using SQL datasets with a SingleStore database:
    • Converting a regular dataset into an SQL dataset generates an invalid query. You have to rewrite it manually to make it work.
    • SQL datasets are not supported if the query contains one of these data types: TINYINT, FLOAT, DECIMAL, DOUBLE UNSIGNED.
    • The SingleStore YEAR data type is converted to STRING when converting to SQL dataset.
  • We do not currently support multiple hosts for SingleStore JDBC URLs.
  • Support for Keys: Primary and foreign keys are not supported when generating a logical data model (LDM). As an alternative, database naming conventions may be used to automatically generate primary keys and references into the LDM.
  • Common Table Expressions (CTEs): The show missing values feature will not work due to limitations with CTEs.
  • Unsupported Data Types:
    • The following data types are not supported: LONGBLOB, MEDIUMBLOB, BLOB (except TINYBLOB), TIME, GEOGRAPHYPOINT, JSON, RECORD, ARRAY.
    • The YEAR data type cannot render data (similar issue as with MariaDB).
Oracle Database
GoodData ADS