Domo API to MS-SQL

ArborRose

I tried unsuccessfully to find an example of pulling a Domo dataset directly to MS-SQL via the API. So I created my own. I am not a python coder but my script works just fine. Does anyone have a version of this subroutine that does not require defining the column names (ie header)?

#Retrieve dataset from Domo API

def get_domo_dataset(dataset_id):
dataset_url = f'https://api.domo.com/v1/datasets/' + dataset_id + '/data'
headers = {'Authorization': f'Bearer {token}'}
response = requests.get(dataset_url, headers=headers)
if response.status_code == 200:
try:
data = response.content.decode('utf-8')
headers = 'field_1','field_2','field_3'
reader = csv.reader(io.StringIO(data))
dataset = [dict(zip(headers, row)) for row in reader]
return dataset
except csv.Error as e:
print('Failed to parse CSV data:', str(e))
return None
else:
print('Failed to retrieve dataset from Domo API:', response.text)
return None

DomoDork

@ArborRose - I have an idea for you that may make your life easier.

Domo has an ODBC driver. I'm betting you could install that on the machine your SQL server sits on then configure a linked server on the SQL Server side. That way, you can query and view Domo data live using standard MS SQL. I havent done it myself, but we used to use MS SQL before Domo and if some external datasource had ODBC drivers, we'd link them directly into our SQL Server to make it much easier on ourselves. We could then just schedule a SQL server job to select data out of a given source and upsert it into a SQL table.

ODBC Driver:

https://domo-support.domo.com/s/article/360043437693?language=en_US

Creating a linked server in MSSQL to an ODBC datasource:

https://www.sqlshack.com/configure-a-linked-server-between-sql-server-and-postgresql-using-odbc-drivers/

ArborRose

Just realized I already have a script that I use to export csv files - that pulls me the dataset with the headers.

DomoDork

@ArborRose - I have an idea for you that may make your life easier.

Domo has an ODBC driver. I'm betting you could install that on the machine your SQL server sits on then configure a linked server on the SQL Server side. That way, you can query and view Domo data live using standard MS SQL. I havent done it myself, but we used to use MS SQL before Domo and if some external datasource had ODBC drivers, we'd link them directly into our SQL Server to make it much easier on ourselves. We could then just schedule a SQL server job to select data out of a given source and upsert it into a SQL table.

ODBC Driver:

https://domo-support.domo.com/s/article/360043437693?language=en_US

Creating a linked server in MSSQL to an ODBC datasource:

https://www.sqlshack.com/configure-a-linked-server-between-sql-server-and-postgresql-using-odbc-drivers/

ArborRose

@Domodork - I like the idea. I may use the ODBC driver at some point in the future. This API call is a special use case that we want to go through the API.