Create and Test Data Sources

To learn what databases you can integrate with GoodData.CN, see Supported Databases.

Create and Manage Data Sources

To create, get, edit, or delete a data source, use the standard CRUD operations and submit an appropriate request to /api/entities/dataSources.

A data source has the following properties:

URL

The URL uses the standard JDBC URL format. For information about how to set up the JDBC URL for your database, review the article about your database under Supported Databases.

• The URL must not be empty.

• The URL must start with jdbc: and be constructed in the following format:

  jdbc:<database-type>://<host>:<port>/<path>?<param-1>=<value-1>&<param-2>=<value-2>

  You can use ; instead of ? and &.

  • The query cannot contain parameters that have been forbidden for security reasons. For more information, review the article about your database under Supported Databases.
  • Depending on the database, the path and query can be optional. For more information, review the article about your database under Supported Databases.
  • The combination of (database type, host, database) cannot be the same as the ones used for hosting internal data storages:
    • Analytics metadata (insights, metrics, …)
    • The internal OIDC identity provider (authentication)
    • Caching metadata (evidence of cache tables and their expiry)

Username and password

The username and password are optional. These can be empty if implicit authentication (for example, AWS Identity and Access Management) is used.

Tokens

Tokens are an alternative to usernames and passwords.

• Example: BigQuery service account encoded by Base64

Review the article about your database under Supported Databases to find out if tokens are an option or a requirement for your set up.

Type

To find out the type, submit a GET request to /api/options/availableDrivers.

Schema

A schema is required. A data source can be connected with only one schema. If you want to access multiple schemas in your database, create a separate data source for each schema.

Enable Caching and Set Cache Path

Support for this caching is in beta. Beta features are available for users to test and provide feedback. They do not have their implementation finalized. The behavior or interface for these features may change in the future.

enableCaching can be set to true and the cachePath to the schema for where caches are stored.

• With enableCaching=false, the platform transforms the execution of each insight into 1 SQL statement and caches the result in our Redis cache.
• With enableCaching=true, the platform transforms the execution of each insight into multiple SQL statements and stores each as table in the cachePath schema.
• Cache tables are stored in the corresponding data source. The user must have privileges to write into that schema.
• Caches can be reused by other insights.
• There is internal cache-gc process garbage collecting those caches.

Example: A sample request body for creating a PostgreSQL data source

{
  "data": {
    "attributes": {
      "name": "demo-ds",
      "url": "jdbc:postgresql://localhost:5432/demo",
      "schema": "demo",
      "type": "POSTGRESQL",
      "username": "demouser",
      "password": "demopass"
    },
    "id": "demo-ds",
    "type": "dataSource"
  }
}

Once you have created data sources for your database, generate a physical data model (PDM).

Test Connectivity to a Database

For any data source, you can test connectivity to the database that is represented by this data source. The data source does not have to be registered in GoodData.CN for connectivity testing.

Registered Data Source

To test connectivity for a registered data source, you can use the demo-ds data source as an example. The demo-ds data source represents the pre-installed PostgreSQL database with the sample data prepared in the GoodData.CN Community Edition image.

Submit a POST request to /api/actions/dataSources/demo-ds/test.

curl $ENDPOINT/api/actions/dataSources/demo-ds/test \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -H "Authorization: Bearer $GDC_API_TOKEN" \
  -d '{}' \
  -X POST | jq .

Invoke-RestMethod -Method Post -Uri "$ENDPOINT/api/actions/dataSources/demo-ds/test" `
  -ContentType 'application/json' `
  -H @{ 
    'Accept' = 'application/json' 
    'Authorization' = "Bearer $GDC_API_TOKEN" 
  } | ConvertTo-Json

Because the pre-installed PostgreSQL database indeed runs behind the demo-ds data source, the server returns the following response:

{
  "successful": true
}

Unregistered Data Source

To test connectivity for an unregistered data source, you can test whether a PostgreSQL database is running on localhost, port 15432.

Submit a POST request to /api/actions/dataSource/test.

curl $ENDPOINT/api/actions/dataSource/test \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -H "Authorization: Bearer $GDC_API_TOKEN" \
  -d '{
      "url": "jdbc:postgresql://localhost:15432/demo",
      "schema": "demo",
      "type": "POSTGRESQL",
      "username": "demouser",
      "password": "demopass"
  }' \
  -X POST | jq .

Invoke-RestMethod -Method Post -Uri "$ENDPOINT/api/actions/dataSource/test" `
  -ContentType 'application/json' `
  -H @{ 
    'Accept' = 'application/json' 
    'Authorization' = "Bearer $GDC_API_TOKEN" 
  } `
  -Body '{
      "url": "jdbc:postgresql://localhost:15432/demo",
      "schema": "demo",
      "type": "POSTGRESQL",
      "username": "demouser",
      "password": "demopass"
  }' | ConvertTo-Json

Because most likely no PostgreSQL database runs on port 15432, the server returns the following response:

{
  "successful": false,
  "error": "Connection exception: Failed to initialize pool: Connection to localhost:15432 refused. Check that the hostname and port are correct and that the postmaster is accepting TCP/IP connections."
}