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
- 2K Product Ideas
- 2K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 311 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 3.8K Transform
- 655 Datasets
- 114 SQL DataFlows
- 2.2K Magic ETL
- 811 Beast Mode
- 3.3K Visualize
- 2.5K Charting
- 80 App Studio
- 45 Variables
- 771 Automate
- 190 Apps
- 481 APIs & Domo Developer
- 77 Workflows
- 23 Code Engine
- 36 AI and Machine Learning
- 19 AI Chat
- AI Playground
- AI Projects and Models
- 17 Jupyter Workspaces
- 409 Distribute
- 120 Domo Everywhere
- 279 Scheduled Reports
- 10 Software Integrations
- 142 Manage
- 138 Governance & Security
- 8 Domo Community Gallery
- 48 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 41 Getting Started
- 31 Community Member Introductions
- 114 Community Announcements
- 4.8K Archive