Connect Data

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

GoodData supports the following databases:

Amazon Redshift
PostgreSQL Server
Snowflake
Vertica
Google BigQuery (currently supported only in GoodData.CN)

GoodData supports the following data sources:

Apache Drill
Dremio

Do you want to know how we manage countless different data sources? Read the article why GoodData decided to integrate with Dremio.

Example Database

The data represents 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 has the following physical schema of tables and their relationships:

Demo data

You can connect to the example snowflake database from GoodData Cloud using the following credentials:

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

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

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

Create a Data Source

To integrate your database into GoodData, you 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.

A data source is a logical object in GoodData that represents the database where your source data is stored.

When you create a data source for your database, in UI or with API calls you can scan the database, transforms its metadata to a declarative definition of the physical data model (PDM), and stores the PDM under the data source entity. You then build a logical data model (LDM) from the stored PDM.

Python

API (Bash)

API (Powershell)

In the example below, we connect the snowflake example database. If you want to use your database, please fill up your connection details.

Open the demo workspace on the tab Data and click the Create model button.
In the left panel in section DATA SOURCES, click the + icon.
The following modal window will open, and you can select your desired database or data source manager.
After selecting of desired database or data source manager, you need to fill up connection details and click the connect button.
The last step is to fill up the schema name, as it is in the following dialog.
After click of the button Save, the database will be connected.
To confirm that the database has been connected, you should see it in the left panel as a new data source:

In the example below, we connect the snowflake example database. If you want to use your database, please fill up your connection details.

You can connect a database with 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) data source using general interface 
# can be used for any type of data source
# If 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="GOODDATA_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 single data source
data_sources = sdk.catalog_data_source.get_data_source('demo-ds')

In the example below, we connect the snowflake example database. If you want to use your database, please fill up your connection details.

You can connect a database with the following API call:

curl $ENDPOINT/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": "GOODDATA_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": "GOODDATA_DEMO_SCHEMA",
      "username": "gooddata_demo"
    }
  },
  "links": {
    "self": "$ENDPOINT/api/v1/entities/dataSources/demo-ds"
  }
}

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

Invoke-RestMethod -Method Post -Uri '$ENDPOINT/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": "GOODDATA_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": "GOODDATA_DEMO_SCHEMA",
      "username": "gooddata_demo"
    }
  },
  "links": {
    "self": "$ENDPOINT/api/v1/entities/dataSources/demo-ds"
  }
}

Create a Workspace

Build a Logical Data Model

Learn more:

Connect Data

Example Database

Create a Data Source