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:

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

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

• Dremio
• Apache Drill

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:

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.

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.

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")]
    )
)

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')

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": "GOODDATA_DEMO_SCHEMA",
              "type": "SNOWFLAKE",
              "username": "gooddata_demo",
              "password": "4m62f7hSXAayAisZ"
          },
          "id": "demo-ds",
          "type": "dataSource"
      }
  }' | jq .

{
  "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": "$HOST_URL/api/v1/entities/dataSources/demo-ds"
  }
}

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": "GOODDATA_DEMO_SCHEMA",
              "type": "SNOWFLAKE",
              "username": "gooddata_demo",
              "password": "4m62f7hSXAayAisZ"
          },
          "id": "demo-ds",
          "type": "dataSource"
      }
  }' | ConvertTo-Json

{
  "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": "$HOST_URL/api/v1/entities/dataSources/demo-ds"
  }
}