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 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.

• Username and password

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

• Token

  • 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

  • If not specified, the default schema (for example, PUBLIC) is used.
  • A data source can be connected with only one schema. If you want to access multiple schemata in your database, create a separate data source for each 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.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."
}