Connect Data

Before you can start running analytical queries on data stored in a database, you have to connect the database to your GoodData workspace. You can connect to a sample database hosted by GoodData or connect your own data.

GoodData supports the following databases:

  • Snowflake
  • Amazon Redshift
  • Google BigQuery
  • PostgreSQL
  • Vertica
  • Microsoft SQL Server
  • Azure SQL
  • Synapse SQL
  • Databricks
  • Greenplum
  • ClickHouse

As a beta feature, GoodData also supports connections to the following data source managers, which will let you connect to even more types of databases:

  • Dremio
  • Apache Drill

Sample Database

The data represent a simple scenario of an e-commerce system that tracks customers, how they order certain products, and how much money was spent on various marketing campaigns in different campaign channels.

The data are stored and organized according to the following physical schema of tables and their relationships:

Sample data structure

You can connect GoodData Cloud to the sample Snowflake database using the following credentials:

  • Account Name: gooddata
  • Username: gooddata_demo
  • Password: 4m62f7hSXAayAisZ
  • Database name: GOODDATA_DEMO_DATABASE
  • Warehouse: GOODDATA_DEMO_WAREHOUSE
  • Schema name: ECOMMERCE_DEMO_SCHEMA

You can connect GoodData Cloud Native to the sample PostgreSQL database using the following credentials:

  • Connection URL: jdbc:postgresql://localhost:5432
  • Username: demouser
  • Password: demopass
  • Database name: demo
  • Schema name: ecommerce_demo

Create a Data Source

To integrate your database into GoodData, you have to connect it to a workspace. As a result, a new data source is registered as an entity in GoodData and you can reuse it in multiple workspaces.

UI
Python
API (Bash)
API (Powershell)

In the example below, we connect the Snowflake sample database. If you want to use your own database, fill in your connection details.

  1. On the homepage, select the Data sources tab and click Connect data.
The Data sources tab
  1. Select the type of database you are using.
The Connect data dialog
  1. Name your data source, fill in the connection details, and click Connect.
The Data source dialog
  1. Fill in the schema name and click Save button.
The Connection schema

The database is connected:

The Data sources tab with a connected database

In the example below, we connect the Snowflake sample database. If you want to use your own database, fill in your connection details.

You can connect a database using the following Python code:

from gooddata_sdk import GoodDataSdk, CatalogDataSource, BasicCredentials

# GoodData host in the form of uri eg. "https://*.gooddata.com" (GoodData Cloud), 
# or "http://localhost:3000" (GoodData Cloud Native)
host = "<GOODDATA_URI>"
# GoodData API token
token = "<API_TOKEN>"
sdk = GoodDataSdk.create(host, token)

# Create (or update) a data source using a general interface. 
# It can be used for any type of data source.
# If a data source already exists, it is updated.
sdk.catalog_data_source.create_or_update_data_source(
    CatalogDataSource(
        id="demo-ds",
        name="demo-ds",
        data_source_type="SNOWFLAKE",
        url="jdbc:snowflake://gooddata.snowflakecomputing.com?warehouse=GOODDATA_DEMO_WAREHOUSE&db=GOODDATA_DEMO_DATABASE",
        schema="ECOMMERCE_DEMO_SCHEMA",
        credentials=BasicCredentials(
            username="gooddata_demo",
            password="4m62f7hSXAayAisZ",
        ),
        enable_caching=False,
        url_params=[("param", "value")]
    )
)

To confirm that the database has been connected, you can run the following code:

from gooddata_sdk import GoodDataSdk

# GoodData host in the form of uri eg. "https://*.gooddata.com" (GoodData Cloud), 
# or "http://localhost:3000" (GoodData Cloud Native)
host = "<GOODDATA_URI>"
# GoodData API token
token = "<API_TOKEN>"
sdk = GoodDataSdk.create(host, token)

# Get a single data source
data_sources = sdk.catalog_data_source.get_data_source('demo-ds')

In the example below, we connect the Snowflake sample database. If you want to use your own database, fill in your connection details.

You can connect a database using 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": {
          "attributes": {
              "name": "demo-ds",
              "url": "jdbc:snowflake://gooddata.snowflakecomputing.com?warehouse=GOODDATA_DEMO_WAREHOUSE&db=GOODDATA_DEMO_DATABASE",
              "schema": "ECOMMERCE_DEMO_SCHEMA",
              "type": "SNOWFLAKE",
              "username": "gooddata_demo",
              "password": "4m62f7hSXAayAisZ"
          },
          "id": "demo-ds",
          "type": "dataSource"
      }
  }' | jq .

To confirm that the database has been connected, the server returns the following response:

{
  "data": {
    "id": "demo-ds",
    "type": "dataSource",
    "attributes": {
      "name": "demo-ds",
      "type": "SNOWFLAKE",
      "url": "jdbc:snowflake://gooddata.snowflakecomputing.com?warehouse=GOODDATA_DEMO_WAREHOUSE&db=GOODDATA_DEMO_DATABASE",
      "schema": "ECOMMERCE_DEMO_SCHEMA",
      "username": "gooddata_demo"
    }
  },
  "links": {
    "self": "$HOST_URL/api/v1/entities/dataSources/demo-ds"
  }
}

You can connect your own database using the following API call:

Invoke-RestMethod -Method Post -Uri '$HOST_URL/api/v1/entities/dataSources' `
  -ContentType 'application/vnd.gooddata.api+json' `
  -H @{ 
    'Accept' = 'application/vnd.gooddata.api+json' 
    'Authorization' = 'Bearer <API_TOKEN>' 
  } `
  -Body '{
      "data": {
          "attributes": {
              "name": "demo-ds",
              "url": "jdbc:snowflake://gooddata.snowflakecomputing.com?warehouse=GOODDATA_DEMO_WAREHOUSE&db=GOODDATA_DEMO_DATABASE",
              "schema": "ECOMMERCE_DEMO_SCHEMA",
              "type": "SNOWFLAKE",
              "username": "gooddata_demo",
              "password": "4m62f7hSXAayAisZ"
          },
          "id": "demo-ds",
          "type": "dataSource"
      }
  }' | ConvertTo-Json

To confirm that the database has been connected, the server returns the following response:

{
  "data": {
    "id": "demo-ds",
    "type": "dataSource",
    "attributes": {
      "name": "demo-ds",
      "type": "SNOWFLAKE",
      "url": "jdbc:snowflake://gooddata.snowflakecomputing.com?warehouse=GOODDATA_DEMO_WAREHOUSE&db=GOODDATA_DEMO_DATABASE",
      "schema": "ECOMMERCE_DEMO_SCHEMA",
      "username": "gooddata_demo"
    }
  },
  "links": {
    "self": "$HOST_URL/api/v1/entities/dataSources/demo-ds"
  }
}