How to connect external sources through catalog API

1

Hi ,
I am new to dremio. I am trying to add a postgres database as an external source in dremio by running a python code. while calling catalog API, I am passing entityType as ‘source’.
But it is saying - “Unrecognized token ‘entityType’: was expecting (JSON String, Number, Array, Object or token ‘null’, ‘true’ or ‘false’)”.

Can you guide me with any link or document which will tell me what all I need to pass in the json payload for adding an external source?

2

@prad What is the exact API call you are making?

3

Hi Balaji,
I have been trying with lot of json structure. As of now I did not find any document/links to guide what will be the exact json payload structure. Below is the payload.
db_creation_payload = {
“entityType”: “source”,
“config”: {
“scriptsEnabled”: “true”,
“showHiddenIndices”: “false”,
“showIdColumn”: “false”,
“readTimeoutMillis”: 60000,
“scrollTimeoutMillis”: 300000,
“usePainless”: “true”,
“scrollSize”: 4000,
“allowPushdownOnNormalizedOrAnalyzedFields”: “false”,
“warnOnRowCountMismatch”: “false”,
“encryptionValidationMode”: “CERTIFICATE_AND_HOSTNAME_VALIDATION”,
“hostList”: [
{
“hostname”: “localhost”,
“port”: 5432
}
],
“authenticationType”: “ANONYMOUS”,
“sslEnabled”: “false”,
“useWhitelist”: “false”
},
“type”: “POSTGRES”,
“name”: “dremio”,
“metadataPolicy”: {
“authTTLMs”: 86400000,
“namesRefreshMs”: 3600000,
“datasetRefreshAfterMs”: 3600000,
“datasetExpireAfterMs”: 10800000,
“datasetUpdateMode”: “PREFETCH_QUERIED”,
“deleteUnavailableDatasets”: “true”,
“autoPromoteDatasets”: “false”
},
“accelerationGracePeriodMs”: 10800000,
“accelerationRefreshPeriodMs”: 3600000,
“accelerationNeverExpire”: “false”,
“accelerationNeverRefresh”: “false”
}

Below is the function calling API.

def apiPost(endpoint, hdr, body=None):
text = requests.post(’{server}/api/v3/{endpoint}’.format(server=dremioServer, endpoint=endpoint), headers=hdr,
data=json.dumps(body)).text
print(text)

hdr = login(username, password)
apiPost(“catalog”, hdr, db_creation_payload)

Now it is not giving unrecognised entityType. Now it is showing -
"Unrecognized field “scriptsEnabled” (class com.dremio.exec.store.jdbc.conf.PostgresConf), not marked as ignorable (15 known properties: “port”, “useSsl”, “databaseName”, “maxIdleConns”, “idleTimeSec”, “secretResourceUrl”, “authenticationType”, “hostname”, “username”, “queryTimeoutSec”, “password”, “encryptionValidationMode”, “propertyList”, “useLegacyDialect”, “fetchSize”])
at [Source: (org.glassfish.jersey.message.internal.ReaderInterceptorExecutor$UnCloseableInputStream); line: 1, column: 533] (through reference chain: com.dremio.exec.store.jdbc.conf.PostgresConf[“scriptsEnabled”])
"

Any help regarding the exact json payload will be really helpful for me.

4

@prad It looks like you are using python. Can you try using code similar to the following? I generated this code using Postman and tested myself. Make sure to set the correct URL for the Dremio coordinator and use your auth token.

import requests
import json

url = “http://localhost:9047/api/v3/catalog”

payload = json.dumps({
“entityType”: “source”,
“config”: {
“scriptsEnabled”: True,
“showHiddenIndices”: False,
“showIdColumn”: False,
“readTimeoutMillis”: 60000,
“scrollTimeoutMillis”: 300000,
“usePainless”: True,
“scrollSize”: 4000,
“allowPushdownOnNormalizedOrAnalyzedFields”: False,
“warnOnRowCountMismatch”: False,
“encryptionValidationMode”: “CERTIFICATE_AND_HOSTNAME_VALIDATION”,
“hostList”: [
{
“hostname”: “localhost”,
“port”: 5432
}
],
“authenticationType”: “ANONYMOUS”,
“sslEnabled”: False,
“useWhitelist”: False
},
“type”: “POSTGRES”,
“name”: “postgres”,
“metadataPolicy”: {
“authTTLMs”: 86400000,
“namesRefreshMs”: 3600000,
“datasetRefreshAfterMs”: 3600000,
“datasetExpireAfterMs”: 10800000,
“datasetUpdateMode”: “PREFETCH_QUERIED”,
“deleteUnavailableDatasets”: True,
“autoPromoteDatasets”: False
},
“accelerationGracePeriodMs”: 10800000,
“accelerationRefreshPeriodMs”: 3600000,
“accelerationNeverExpire”: False,
“accelerationNeverRefresh”: False
})
headers = {
‘Authorization’: ‘_dremio’,
‘Content-Type’: ‘application/json’
}

response = requests.request(“POST”, url, headers=headers, data=payload)

print(response.text)

5

Hi Brock,
Thanks for the testing. Now API is accepting the payload. But it does not resolve my original issue i.e adding an external sorce. While adding an source through dremio UI, we used to give source name, server, id, password. Also We have to check “enable legacy dialect”.
There is no such key option in this json structure. The response is also 400 . I guess this is because there is no password in side the payload to add external source.
Can you please help here with correct json structure?

Thanks

6

The options for each source are documented here: Dremio

A good way to see the options is to run a GET on the /catalog/{id} API for that source. Here is an example output.

{

"entityType": "source",

"config": {

    "hostname": "metastore-db",

    "port": "5432",

    "databaseName": "metastore_db",

    "username": "admin",

    "password": "$DREMIO_EXISTING_VALUE$",

    "authenticationType": "MASTER",

    "fetchSize": 200,

    "useSsl": false,

    "encryptionValidationMode": "CERTIFICATE_AND_HOSTNAME_VALIDATION",

    "maxIdleConns": 8,

    "idleTimeSec": 60,

    "queryTimeoutSec": 0

},

"state": {

    "status": "good",

    "suggestedUserAction": "",

    "messages": []

},

"id": "9557c73d-325f-4dc0-932d-627e50d8eac3",

"tag": "DYSelbkS2QQ=",

"type": "POSTGRES",

"name": "postgre",

"createdAt": "2022-01-06T15:42:19.074Z",

"metadataPolicy": {

    "authTTLMs": 86400000,

    "namesRefreshMs": 3600000,

    "datasetRefreshAfterMs": 3600000,

    "datasetExpireAfterMs": 10800000,

    "datasetUpdateMode": "PREFETCH_QUERIED",

    "deleteUnavailableDatasets": true,

    "autoPromoteDatasets": false

},

"accelerationGracePeriodMs": 10800000,

"accelerationRefreshPeriodMs": 3600000,

"accelerationNeverExpire": false,

"accelerationNeverRefresh": false,

"children": [

    {

        "id": "7746069f-caad-4446-b666-0c669dad54b5",

        "path": [

            "postgre",

            "public"

        ],

        "tag": "Q5EmJio9Rps=",

        "type": "CONTAINER",

        "containerType": "FOLDER"

    }

],

"allowCrossSourceSelection": false,

"disableMetadataValidityCheck": false

}

1 Like
7

Thanks. The documentation helped a lot…