SQL Extractor

Owned by Joni Girardi
Last updated: Sept 10, 2024
3 min read

DataSelf API - Beta Version

Many organizations store data in their DataSelf cloud data warehouse(s) for optimized reporting and data analysis. The SQL Extractor allows them to easily and securely download their data for further reporting or manipulation using other tools such as Excel.

The SQL Extractor can extract the list and data from the data warehouse tables and views, as well as extract only a subset of the data, such as certain columns, and apply extract filters.

Endpoint

https://api.beta.dataself.com/dw6/entities/<entity id>/extract?token=<user token>&source=dw&schema=<schema name>&object=<table/view name>&fields=<field name1>,<field name2>&filters[<filter field name1>]=<filter value>

Parameters

  • <entity id>: ETL+ Entity ID, e.g.: abccorp

  • token=<user token>: ETL+ user token - you receive it in your DataSelf Product Registration email. To retrieve it, search your inbox for “DataSelf Product Registration“ or log into your ETL+ → Settings → Client Info → Send → check your email for the product registration.

  • See Core Endpoint for core parameters.

  • schema=<schema name>: Optional. If not used, it assumes the schema is dbo.

  • object=<table/view name>: Optional. If not used, the endpoint returns the list of available tables/views including their row count, bytes, and create date.

  • fields=<field name1>: Optional. Can only be used in conjunction with the table parameter. If not used, the endpoint returns all fields of the table/view. Use commas to separate multiple fields.

  • filters=[<filter field name1>]=<filter value>: Optional. Can only be used in conjunction with the table parameter. When used, it’ll filter the extraction based on the criteria.

Example

  • The list of tables/views: https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx

  • A table/view from the erp schema: https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&schema=erp&object=ITMMVT

  • A table/view from the dbo schema: https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&object=ITMMVT

  • Some columns from a table/view: https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&object=ITMMVT&fields=AVC_0,ITMREF_0,ROWID

  • A filtered table/view: https://api.beta.dataself.com/dw6/entities/abccorp/extract?source=dw&token=xx&object=ITMMVT&filters[ITMref_0]=Fin063

Output options

  • CSV download: use the endpoint on a web browser. You might need to right-click the text and Save As into a local CSV file.

  • Excel: go to the Data ribbon → From Web → paste the endpoint:

    • Click Ok.

    • Set the Delimiter to Comma.

    • If the table has undergone structural changes since the last extraction on this Excel install (cache), click the Refresh icon.

    • Click Load.

    • If you see errors on the Queries & Connections panel, mouse over the errors section and click on it to open the Power Query Editor. Scan through the shown tables for “Error”, click on them to get a description on the bottom panel. The most common fix to these errors is by going to the main query in Power Query → select Changed Type on the right panel → edit the Table.TransformColumnType expression (drop down at the top of the table), for instance, a column set to Inte64.Type must be changed to type text.

  • Power BI: Get data → Web → paste the endpoint → Ok → Anonymous → Connect:

    • Set the Delimiter to Comma.

    • If the table has undergone structural changes since the last extraction on this Excel install (cache), click the Refresh icon.

    • Click Load.

    • If you see errors on the Load panel, click View errors to open the Power Query Editor. Scan through the shown tables for “Error”, click on them to get a description on the bottom panel. The most common fix to these errors is by going to the main query in Power Query → select Changed Type on the right panel → edit the Table.TransformColumnTypes expression (drop down at the top of the table), for instance, a column set to Inte64.Type must be changed to type text.

Security

  • IP Whitelisting: This feature only works behind DataSelf VPN or via IP whitelisting. Contact DataSelf support to whitelist your public IP.

  • ETL+ User: The ETL+ API user token ties users to their ETL+ entities and data warehouses.

  • SSL enabled.

Notes/Issues

  • With the beta API, some extracted characters (such as commas and line breaks) can mess up the CSV file. Here’s a SQL transformation to clean up some of them: REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Column1)), CHAR(13), ''), CHAR(10), ''), ',', ''), '"', '""')

  • Endpoint syntax is case-sensitive, except for the parameters.

  • The order of the parameters after ? is not relevant.