Analytics » Upload Tool » Uploading Data from Cloud databases
If you have any applications that stores data in cloud databases like SQL Azure and Amazon RDS, then you can use Upload Tool to pull data from your databases and upload the same into Virtutem Analytics.
In this section, we will discuss about how to upload data from your cloud databases into Virtutem Analytics. You can also read about how to configure the Upload tool to periodically upload/synchronize the data from your databases into Virtutem Analytics s.
Virtutem Analytics Upload Tool can be downloaded from this page.
You learn more on installing the upload tool and configuring the common parameters (like proxy settings etc.,) for the tool from this page.
You can upload data from cloud databases like Windows SQL Azure and Amazon RDS. To do this, you need to download and install the Upload tool in your local network and set the proxy setting (if your network has a proxy server setup). Ensure that the Upload tool can connect to your cloud database from the machine in which its installed.
Configure the cloud database connection settings in the database_connection_params.conf configuration file, as specified below.
DBTYPE | Specify your cloud database type. For SQL Azure it should be sqlazure. (refer below for Amazon RDS) |
HOSTNAME | Specify the SQL Azure server name. Server name will be just the first part of the fully qualified SQL Azure server URL in the format servername .database.windows.net |
USERNAME | Your login name to access the SQL Azure database in the format username@servername |
PASSWORD | Password for accessing the SQL Azure database. |
DBNAME | Your database name in SQL Azure from which the data is to be uploaded. |
The example in the following screenshot illustrates the connection settings for a SQL Azure database in database_connection_params.conf file.
Once you have configured the above setting, you need to provide the SQL SELECT queries that are to be executed in your cloud database to fetch data and upload the same into Virtutem Analytics . To learn about how to specify these queries and executing the upload tool click here.
If your MySQL, Oracle, Microsoft SQL Server, or PostgreSQL database is hosted in Amazon RDS, then you need to specify the connection setting in the same way as you do it for Local Database Connection Settings. Ensure that the appropriate host name and listening port are configured to allow Uplod Tool to access the hosted database in Amazon RDS.
Once you have configured the above setting, you need to provide the SQL SELECT queries that are to be executed in your cloud database to fetch data and upload the same into Virtutem Analytics .
The database_sql_queries.xml file contains all the settings related to the SQL SELECT Queries that are to be executed in the database for fetching the required data, along with settings about the Virtutem Analytics database and table names into which the data is to be uploaded. To fetch data from your cloud databases, you need to specify SQL SELECT query. These queries will be executed by the upload tool in your cloud database to fetch the necessary records and upload the same into Virtutem Analytics according to the settings provided.
You can provide any number of SQL SELECT queries to execute. Each query is to be provided in a <Query> <Query> XML element. Multiple queries can be grouped within the <Queries> <Queries> element.
The following screenshot illustrates a sample SQL SELECT queries in the database_sql_queries.xml file.
The above queries will fetch data from two tables and import the same into Virtutem Analytics reporting database MyDB . Data from the dbtable1 will be appended into mytable1 in Virtutem Analytics . And data from dbtable2 , where the employee number is less than 1000, will be added into mytable2 after deleting the existing data.
The following is the SQL SELECT query format to be followed to fetch data from your database.
<Query dbname ="virtutem_analytics_dbname" tablename ="virtutem_analytics_tablename" importtype =" APPEND / TRUNCATEADD / UPDATEADD " matchingcols ="matching_cols_for_updateadd" selectcols ="columns_to_import_from_csv" skiptop ="number_of_rows_to_be_skipped">sql_query_ to_be_executed</Query>
As part of the <Query> node, you also need the provide details about the Virtutem Analytics database into which the data that is fetched from executing the query is to be uploaded. The following table explains all the parameters to be provided as part of the <Query> node.
Parameter | Description |
dbname | The Virtutem Analytics database name into which the data is to be uploaded after executing the SQL Query. |
tablename | The Virtutem Analytics table name into which the data is to be uploaded after executing the SQL Query. |
importtype | Set one of the following as Import Type . · APPEND - appends the data to the end of the table. · UPDATEADD - updates existing data records and appends new data records. For this you need to configure ZOHO_MATCHING_COLUMNS in common_params.conf in the conf directory. · TRUNCATEADD - Deletes the existing data and adds new data. |
matchingcols | This is applicable only when the importtype is set to UPDATEADD. |
selectedcols | The column names separated by comma. Only these columns are uploaded from the resultant query data into the online database. |
skiptop | The number of rows to be skipped from the top in the resultant query data before being uploaded. |
sql_query | Specify the SQL SELECT query to be executed in the database for fetching the necessary data. |
The configuration file common_params.conf in /conf directory provides parameters that Upload Tool uses while uploading data. These import settings help Virtutem Analytics properly understand the data being imported.
The following table lists the import parameters that can be configured in the common_params.conf file for uploading data from your cloud database.
Note : You will not find all the below given parameters present in the configuration file (except mandatory ones). In case you do not find them, add them into the file and provide the required value as specified below.
Parameter | Possible Values | Description |
ZOHO_CREATE_TABLE |
| Optional
Default value is false. |
LINES_TO_SEND | Optional | |
ZOHO_THOUSAND_SEPARATOR |
| Optional
Default is 0. |
ZOHO_DECIMAL_SEPARATOR |
| Optional The following is the list of parameter values and the corresponding decimal separator.
Default value is 0. |
SET_EMPTY_FORNULL |
| This parameter is used to specify whether empty value or null should be added in Virtutem Analytics table when the uploaded database contains null value.
Default value is false . |
ZOHO_CANOVERRIDE |
| Optional
Default value is false. |
ZOHO_ON_IMPORT_ERROR |
| This parameter controls the action to be taken in case there is an error during import.
Default value is SETCOLUMNEMPTY |
ON_ERROR |
| Optional This parameter is used to execute the next query, if the current query executed or upload is failed.
Default value is 0 |
ZOHO_TRUNCATE_COLNAMES |
| Optional In case the column names exceeds 100 character, then you can use this parameter to specify whether to truncate the column names to 100 character or to abort the uload.
Default value is FALSE |
Once you have configured the parameters related to the SQL SELECT Queries and Import setting, you can run the UploadFromDB.bat/UploadFromDB.sh file with the required parameters as explained in the following sections to perform the upload. After successful execution, the data will get uploaded to the specified table in Virtutem Analytics database. The last upload details will be logged in the <UploadTool_Home>/logs/tool0.log files. The error details will be logged in the <UploadTool_Home>/logs/error0.log file .
Note: |
---|
In case there is any failure in executing any of the given SQL Query, then the upload tool will abort the execution of that query and the ones that follow. Whatever queries that had been successfully executed and uploaded into Virtutem Analytics till then will not be rolled back. |
In case you do not want to expose your Virtutem Account password or you have registered in Virtutem Analytics using other login such as Google Apps, Gmail, Facebook & Yahoo and do not have a Virtutem user credentials, then you can use AuthToken to upload data.
UploadFromDB.bat <virtutem_login_email_address> -A <authtoken>
· User Email Address (virtutem_login_email_address): | Your Virtutem Analytics account login E-mail ID. |
· AuthToken: | Authentication Token to authenticate access your Virtutem Analytics Account. |
Note: |
---|
Authenticating the user to upload data using AuthToken is supported only in the latest versions (released on June 17th 2013 or after). It is strongly recommended to use latest Upload Tool. To download the latest version of the Upload Tool, click here . |
To know about all supported parameters and the value to be specified, click here .
You can also use login email address and password to authenticate for uploading data as given below.
UploadFromDB.bat <virtutem_login_email_address> <virtutempassword>
· User Email Address (virtutem_login_email_address): | Your Virtutem Analytics account login E-mail ID. |
· Virtutem Password (virtutem_password): | Your Virtutem Analytics account password |
Important Note |
---|
To know about all supported parameters and the value to be specified, click here |
In case the data is been uploaded by a shared user, then it is mandatory to specify the database owner (or database administrator) in the command line as given below.
UploadFromDB.bat <zoho_login_email_address> -A <authtoken> -D <database_owner_login_e-mail_address>"
· User Email Address(virtutem_login_email_address: | Shared users Virtutem Analytics Login E-mail ID. |
· AuthToken: | Shared users' AuthToke. To know how to generate an AuthToken, refer here. You can also authenticate using Virtutem Analytics account password. |
· Database Owner Login E-mail ID (database_owner_login_e-mail_address): | Virtutem Analytics Login E-mail ID of the database owner (or database administrator). |
To know about all supported parameters and the value to be specified, click here .
Note on Specifying Authentication Details: |
---|
You can also specify the authentication details such as Virtutem Analytics Login email id, AuthToken and Database owner login e-mail address in the common_params.conf file (as values for the parameters USER_EMAIL_ADDRESS, AUTHTOKEN and DBOWNER_EMAIL_ADDRESS respectively). To know more on this, click here. Specifying the details in the common_params.conf file reduces the tedious process of repeating these details every time in your command line arguments. In case you specify these values in the command line apart from specifying in the common_params.conf file, then the values provided in the command line will take precedence. |
Parameter Name | Description |
User Email Address(virtutem_login_email_address) | Your Virtutem Analytics account login E-mail ID. |
AuthToken | AuthToken is a permanent token to authenticate a user to access his/her Virtutem Analytics account. Click here to know how to generate AuthToken. |
Password (virtutemanalytics_password) | Password for accessing your Virtutem Analytics account. |
Database Owner Email Address (database_owner_login_e-mail_address) | In case of uploading data in shared database, specify the database owner (or database administrator) login E-mail ID. |
Using Upload Tool you can periodically schedule uploading data from your cloud database. With this mechanism, you can have the latest data from your application synced into Virtutem Analytics , and the reports that you create over this data stay current.
You can setup a periodic schedule for both the CSV Console mode utility as well as the Database Upload utility using the Operating System Scheduler feature as explained below.
UploadFromDB.bat <virtutem_login_email_address> -A <authtoken>
Example
UploadFromDB.bat eduardo@zillum.com bad18eba1ff45jk7858b8ae88a77fa30
To view the data that you have uploaded: