Related Categories

Load from Remote Data Source

Using our Load from Remote interface, you can bring your data into Virtualitics from a SQL database, a URL, or any data source with an ODBC (Open Database Connectivity) driver.

Click the Remote button under the Datasets section that appears when you open the application or when you return to Spreadsheet View. This will open the Load from Remote window, where you can select one of the three remote connection options from the Load From drop down menu.

SQL Connection

This option allows you to quickly connect to a remote database with standard connection options.

Supported Database Types

  • MySQL
  • SQLServer
  • Redshift
    • Requires a driver installation
  • Postgres
  • Hadoop (via Hive or Impala servers)

 

URL

Load a remote data set or VIP project file from a URL.

Supported File Types:

  • CSV – A Comma Separated Values file
  • Excel spreadsheet – Virtualitics will allow users to select the worksheet from the ‘Worksheets’ dropdown with multiple sheets
  • Text file – Note: Must specify the delimiter used to separate values in the Dataset Preview window
  • JSON file – A specific file type for loading in network data (see Network Data Formats)
  • VIP Project – A saved VIP project with a “.vip” extension

 

ODBC (Open Database Connectivity) Configuration

You can connect to any data source for which there exists an ODBC driver.

  • Install and configure the ODBC Driver for the data source to which you want to connect. (See below for an example using Google BigQuery)
  • Open the Microsoft ODBC Data Sources (64-bit) control panel. In this panel you can add a data source and specify a (DSN) Data Source Name in addition to various authentication options.
  • Create the file C:\Users\<user>\Documents\Virtualitics\Settings\odbc.json. The JSON key/value pairs in this file can contain any valid ODBC connection options and can overwrite and/or supplement the configuration from the ODBC Data Sources (64-bit) panel. At a minimum, it must contain the “DSN” option, referencing a configured ODBC Data Source.
    • We offer support for configuring multiple ODBC connections in the same odbc.json file. Please refer to the sample odbc.json file below for formatting examples.

 

Example: Exploring Google BigQuery

To use Google BigQuery, you must have a Google Cloud Platform (GCP) account. If you don’t currently have one you can sign up for a free account.

  • Log into your GCP account, and navigate to the BigQuery service.
  • Install Simba ODBC Driver for Google BigQuery DSN and configure the driver to authenticate with your credentials in Windows ODBC Data Source Administrator. The driver can be found in the System DSN tab.

  • Select User Authentication from the OAuth drop down and click “Sign in…”. This will direct you through your GCP log in and provide you a confirmation code which you copy & paste into the Confirmation Code field.

  • Next, select the Project ID automatically created for you when you open BigQuery.

  • Link your Google BigQuery DSN to VIP by editing your odbc.josn file.

  • After clicking Connect, you can make a query from the Dataset Preview window and load in your data!