Domo API to MS-SQL

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
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **
Best Answer
-
@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:
Creating a linked server in MSSQL to an ODBC datasource:
1
Answers
-
Just realized I already have a script that I use to export csv files - that pulls me the dataset with the headers.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0 -
@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:
Creating a linked server in MSSQL to an ODBC datasource:
1 -
@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.
** Was this post helpful? Click Agree or Like below. **
** Did this solve your problem? Accept it as a solution! **0
Categories
- All Categories
- 1.1K Product Ideas
- 1.1K Ideas Exchange
- 1.2K Connect
- 969 Connectors
- 257 Workbench
- Cloud Amplifier
- 1 Federated
- 2.4K Transform
- 76 SQL DataFlows
- 501 Datasets
- 1.8K Magic ETL
- 2.7K Visualize
- 2.2K Charting
- 375 Beast Mode
- 20 Variables
- 485 Automate
- 103 Apps
- 378 APIs & Domo Developer
- 6 Workflows
- 22 Predict
- 6 Jupyter Workspaces
- 16 R & Python Tiles
- 316 Distribute
- 64 Domo Everywhere
- 252 Scheduled Reports
- 59 Manage
- 59 Governance & Security
- 1 Product Release Questions
- 5K Community Forums
- 37 Getting Started
- 23 Community Member Introductions
- 63 Community Announcements
- 4.8K Archive