Extracting data from Domo

Brandon

What are all of the options to extract data from Domo and import into Oracle?

Can a daily alert be created to extract data to Excel, CSV, or Text file that then can be imported into Oracle.

GrantSmith

Hi @Brandon

You can schedule a report to send an email with a card at a specific time where you can choose to include the data as an attachment if you're wanting to export data that's been processed / visualized.

If you're looking to export raw data you can utilize the Java CLI tool (https://knowledge.domo.com/Administer/Other_Administrative_Tools/Command_Line_Interface_(CLI)_Tool) - specifically the export-data or export-to-s3 commands.

Alternatively if you're more technical / programmatical you can interface with the public API endpoints with the PyDomo (Python - https://github.com/domoinc/domo-python-sdk) or the RDomo (R - https://github.com/domoinc/rdomo) packages to extract the data. You could then subsequently upload the data to Oracle within the same scripts to simplify your data pipeline.

jaeW_at_Onyx

in addition to @GrantSmith 's answers you can take a look at the ODBC connector, https://knowledge.domo.com/Connect/Connecting_to_Data_Using_Other_Methods/Domo_ODBC_Data_Driver, there are commercial implications to aquiring access to the driver,but it does create a more SQL friendly workflow.

PyDomo would probably be the easiest option. In this tutorial we're executing a dataflow, but it's relatively easy to switch the use case / functions used to extract data.

https://www.youtube.com/watch?v=oT5NipvWK1o

regardless of which extraction method you used, I recommend you build a dataset view as the datasource and then subset that datasource to the correct columns and apply appropriate filters. you don't really want to be passing large datasets over API if you only need to pass a subset of the data (a few columns, or new rows since yesterday.)

user094816

Hi @jaeW_at_Onyx , I have seen this video it is more technical to me since I am very new in DOMO dev side. I just want to export dataset.

I have dataset with million of rows that need to be exported as CSV/Excel file in automatic way. I was trying Jupiter and python to do the job but not able to achieve it. Can you help me out on this where I can refer proper documentation for exporting data in chunks from DOMO to CSV/Excel multiple files for larger dataset.

I do this currently manually on weekly basis. While doing manually I make sure that each exported file having less then 1 million rows due to excel limit. I filter my date based on date filter in domo card.

These individual file will be consumed by someone else in my team.

jaeW_at_Onyx

@user094816 , between @GrantSmith and me, we linked plenty of proper documentation. Can you ask a more specific question about what you're having a challenge with?

FWIW, I'm working on a consulting project where I am using export-data in the Java CLI to extract the data i need. Initially we used query-data but it struggles with massive datasets (i think around 5M+) so we switched to export-data.

query-data accepts MySQL formatted SQL queries, which was nice, but export-data requires DQL (JSON formatted Domo Query Language). You can use convert-sql to convert SQL into a DQL.

dgsinclair

What's maddening is that with table cards, you can export to XLS and have all the data in one document, including the conditional formatting.

Why is THAT no available as a report? The reports/publishing merely output a jpg of the first page of data, which is largely useless. If you can export such things, why are they not available as a schedulable report?

BSovers

Is there documentation of the java CLI command export-data?

https://domohelp.domo.com/hc/en-us/articles/360043437733-Command-Line-Interface-CLI-Tool#2.3.8. seems to describe everything except the export-data command.