SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Data Virtualization Tools and Software Denodo Platform and Data Virtuality Tutorials and Downloads for SQL Database Developer

Data Virtuality SQL Query for Online CSV Data using Web Service Connector


Data virtualization architects and developers can consume and query data published as CSV from a web URL using Data Virtuality. In this Data Virtuality tutorial I want to show how to connect to a CSV data file using generic web service connector invokeHTTP producedure and use standard SQL syntax to query this comma seperated values data from remote web service.

Data Virtuality and CSV files

If you search on the web, especially data scientists will find a lot of resources publishing many statistics as CSV as publibly available like stats.govt.nz
Especiall Google's Dataset Search tool is great for finding public data available on the web.
For the sake of this tutorial, I will use the English Dictionary data set published in CSV format via URL CSV data file

First, launch Data Virtuality Studio.
Since we are going to use invokeHTTP producedure, first we need to create a web service data source using the Web Service connector on Data Virtuality server.
If you have created any web service data source before, you can use that web service and call the invokeHTTP producedure of that previously created web service. In this case you can skip this web service data source creation step.


Create Web Service Data Source for CSV File

In order to create a web service data source, right click on "Data Sources" node within Data Explorer window. From the context menu, select option "Add data source"

create data source for online CSV files on Data Virtuality

Select "Web Service" data source type on the first dialog screen of the data source creation wizard

web service data source type

Continue by pressing "Next" button to next screen for entering data source parameters for the new web service we are creating on Data Virtuality.

An Alias type a descriptive name for your new resource. For example, ws_English for our English dictionary CSV file.

For the End point, we can paste the CSV file download URL.
That is: https://www.bragitoff.com/wp-content/uploads/2016/03/dictionary.csv

Since that is public and can be accessed anonymously, keep "Security type" as "None" which is the default option

Leave all other options as default

Data Virtuality web service data source

Click Next and then Finish to complete web service data source creation to consume the CSV file on Data Virtuality.
When you get "Data source added" message, click OK to close the data source creation wizard.


Query CSV Data using CSV Query Builder Tool

Now, using one of the the web service data sources created on Data Virtuality server, we will now query CSV data and create a SQL View object for later use.

Let's open the SQL Editor. You can use "Alt+E" or use the icon SQL Editor

Open CSV Query Builder tool using the icon CSV Query Builder

When CSV Query Builder tool is launched, switch to Web service tab.
For Data Source, choose the web service data source we have just created, or one of the previously created web services that you have access on the Data Virtuality server.

For End Point, be sure that it is the CSV file download URL

If you have downloaded the file on to your computer and checked the CSV data file, you will see whether it has a header line or you will see which character is used as the seperator character for the CSV file. Using your observation on the CSV file, you can configure Dynamic parsing section. In most cases, default options are correct. But for this English words list, there is not a header line, so I unmark this checkbox next to Header.

When you click Apply button the tool will analyze the source data.
As a result of the data source analyze, it will display the columns data and provide some sample data rows parsed accordingly.

CSV Query Builder to preview CSV file data

For most cases when there is a header, we can directly close the CSV Query Builder application.
But in this case it is better to rename the data columns as follows:

rename CSV header columns for SQL query

Click OK to close the wizard

When the CSV Query Builder tool closes, it created automatically the SQL code we need to access online CSV data using the web service data source created in previous steps.

SQL code to query online CSV file on Data Virtuality server

SQL programmers and data virtualization platform developers can modify following SQL code for different CSV files. Although this is an option for SQL developer, using the CSV Query Builder tool will visualize the data with preview option and enable use all configuration options for the data source.

SELECT
 "csv_table"."Word"
 ,"csv_table"."Tip"
 ,"csv_table"."Meaning"
FROM
(
 exec "ws_English".invokeHTTP (
  endpoint => 'https://www.bragitoff.com/wp-content/uploads/2016/03/dictionary.csv'
  ,action => 'GET'
  ,requestContentType => 'application/xml'
 )
) w
 ,TEXTTABLE (
 to_chars (
  w.result
  ,'UTF-8'
 ) COLUMNS "Word" STRING
  ,"Tip" STRING
  ,"Meaning" STRING DELIMITER ',' QUOTE '"'
 ) "csv_table";;

Let's now create a SQL View using above SQL Select statement.
You will need a Virtual Schema for this. If you have previously created one, you can also use that virtual schema, too

All you need to do is adding the following first two lines of SQL code "CREATE VIEW VirtualSchema.ViewName AS" in front of the above SQL Select query

Create View Utilities.English_Words_List
AS

SELECT
 "csv_table"."Word"
 ,"csv_table"."Tip"
 ,"csv_table"."Meaning"
FROM
(
 exec "ws_English".invokeHTTP (
  endpoint => 'https://www.bragitoff.com/wp-content/uploads/2016/03/dictionary.csv'
  ,action => 'GET'
  ,requestContentType => 'application/xml'
 )
) w
 ,TEXTTABLE (
 to_chars (
  w.result
  ,'UTF-8'
 ) COLUMNS "Word" STRING
  ,"Tip" STRING
  ,"Meaning" STRING DELIMITER ',' QUOTE '"'
 ) "csv_table";;

Now I can only refer to view without using all underelying web service invokeHTTP procedure execution codes as follows to query specific words in English for their meaning

SELECT "Word", "Tip", "Meaning"
FROM "Utilities.English_Words_List"
WHERE "Word" LIKE 'Eagle%';;

Data Virtuality SQL query for online CSV files

To summarize, data virtualization platform architects can create a single general web service data source for CSV files from different web resources. Data Virtuality developers can use this web service connector with the help of CSV Query Builder tool to fetch the CSV data or to execute SQL queries on the online data.

I hope data professionals working on Logical Data Warehouse solution Data Virtuality will find this tutorial useful.



Data Virtualization


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.