Create and Test Data Sources

To learn what databases you can integrate with GoodData, 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/v1/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 (visualizations, 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/v1/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 Pre-aggregation Caching

Support for this form of 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.

We recommend that you first read the Cache Management article to gain an understanding of what pre-aggregation caching does and what is it good for.

Pre-aggregation tables are stored in the data source. You must therefore ensure that the user has privileges to write into the data source schema that you want to use as a pre-aggregation cache. For more information, see Supported Databases, find the data source that you are using and refer to its User Access Rights section.

To enable pre-aggregation caching, use the /api/v1/entities/dataSources/{dataSourceId} API to set enableCaching to true and set cachePath to the schema where you want your pre-aggregation cache to be stored. Your API call should look like this:

curl -X PATCH $HOST_URL/api/v1/actions/dataSources/<your-data-source> \
-H 'Content-Type: application/vnd.gooddata.api+json' \
-H "Accept: application/json" \
-H 'Authorization: Bearer $API_TOKEN' \
-d '{
  "data": {
    "type": "dataSource",
    "id": "<your-data-source>",
    "attributes": {
      "enableCaching": true,
      "cachePath": [
        "<your-cache-schema>"
      ]
    }
  }
}'

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 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/v1/actions/dataSources/demo-ds/test.

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

Invoke-RestMethod -Method Post -Uri "$HOST_URL/api/v1/actions/dataSources/demo-ds/test" `
  -ContentType 'application/json' `
  -H @{ 
    'Accept' = 'application/json' 
    'Authorization' = "Bearer $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/v1/actions/dataSource/test.

curl $HOST_URL/api/v1/actions/dataSource/test \
  -H "Content-Type: application/json" \
  -H "Accept: application/json" \
  -H "Authorization: Bearer $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 "$HOST_URL/api/v1/actions/dataSource/test" `
  -ContentType 'application/json' `
  -H @{ 
    'Accept' = 'application/json' 
    'Authorization' = "Bearer $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."
}