JDBC Driver Supported Databases

Uploading Data from JDBC Driver Supported NoSQL Databases

Zoho Reports Upload Tool is a downloadable standalone utility, which you can install in your local environment and connect to upload data from your local databases behind firewall into Zoho Reports. If you have stored your data in JDBC supported NoSQL database, such as Apache Cassandra, Apache Hadoop Hive, Cloudera Hadoop Hive, etc., then you can use Upload Tool to pull data from your databases and upload the same into Zoho Reports.

In this section, we will discuss about how to connect the Upload Tool to pull data from local/hosted JDBC supported NoSQL database and import into Zoho Reports. You can also read about how to configure the Upload tool to periodically upload/synchronize the data from your databases into Zoho Reports.

Downloading and Installing Upload Tool

Zoho Reports Upload Tool can be downloaded from the link below.

https://www.zoho.com/reports/help/import-upload-data-into-zoho-reports/upload-tool/download-and-setup.html#download-tool

You can easily install the tool by extracting the file in the required location.

Setting up JDBC Driver

You can connect your local/hosted database using JDBC Driver by copying the related JDBC driver(JDBC jar file) of the database into the /lib directory of the Upload Tool. You need to download the corresponding JDBC driver provided by the database vendor. Refer to the vendor's database documentation for details.

Configuring the Upload Tool

The conf directory contains all the configuration files for the tool. Before you execute the tool you need to specify the appropriate configuration settings. The /conf directory consists of three files.

  • common_params.conf - Provides connection parameters to specify the details of the Zoho Reports account into which the data need to be uploaded.
  • database_connection_params.conf - Provides database connection parameters to specify the details of the local/hosted JDBC supported database from which data need to be uploaded.
  • database_sql_queries.xml - Allows you to specify the SQL SELECT queries that are to be used to fetch the data from your local/hosted JDBC supported database and upload the same into Zoho Reports reporting database.

Connection Parameters

The common_params.conf file provides connection parameters to specify the details of the Zoho Reports account into which the data need to be uploaded. Apart from the Zoho Reports Connection Parameters, this also contains Import Parameters and proxy server details.

Zoho Reports Connection Parameters

The following table contains connection parameters for Zoho Reports.

ParameterDescriptionMandatory/Optional

USER_EMAIL_ADDRESS

Specify your Zoho Reports account E-mail ID

Mandatory

AUTHTOKENSpecify your Zoho Reports AuthToken. This is a unique token provided for your account for secure access.

In case you do not have an AuthToken, you can generate one by accessing the below link and login to Zoho Reports.

https://accounts.zoho.com/
apiauthtoken/create?SCOPE=ZohoReports/reportsapi

AuthToken for your account will be generated, as given below (the value for the parameter AUTHTOKEN in the response given below, is the acutal AuthToken for your account.)

#Wed Feb 29 03:07:33 PST 2012
AUTHTOKEN=
bad18eba1ff45jk7858b8ae88a77fa30

RESULT=TRUE

Mandatory

DBOWNER_EMAIL_ADDRESS

Specify the Zoho Reports account Admin E-mail ID here.

Mandatory

To connect the tool to the internet through a proxy server, you need to provide the following. If you are using a Direct Internet connection, then this setting can be ignored.

ParameterDescriptionMandatory/Optional

USEPROXY

Set this to true, if you are connected through a Proxy server.

Set this to false, if you are connected to the Internet directly. By default this value is set to false.

Mandatory

PROXYHOSTSpecify the machine name or IP address where the proxy server is running in your network.

Mandatory

PROXYPORT

Specify the port in which the proxy server is running.

Mandatory
PROXYUSERNAME

Specify your proxy server user name to access the proxy server.

Mandatory
PROXYPASSWORD

Specify your proxy password to access the proxy server.

Mandatory

Import Parameters

These import settings help Zoho Reports properly understand the data being imported. You need not have to configure these parameters unless you want to fine tune the import process. The default settings should suffice for most cases.

These parameters are available in the common_params.conf file. To know more about the available Import Parameters and how to set them, refer here.

Database Connection Parameters

You can configure the settings to connect to your local/hosted JDBC supported database in the database_connection_params.conf file.

Specify the following connection parameters.

DBTYPESpecify your database name in this field (Eg., cassandra or apachehive or clouderahive or hortonworkshive)
DRIVERCLASSNAMESpecify the JDBC driver class name of the database (refer to the table in the below section)
CONNECTIONURLSpecify database URL with or without username and password to establish connection with the local database (refer to the table above).
USERNAMESpecify the USERNAME to access the database. This is not required if you have specified the authentication details in CONNECTIONURL itself.
PASSWORSDSpecify the PASSWORD to access the database. This is not required if you have specified the authentication details in CONNECTIONURL itself.

JDBC Driver Supported NoSQL Databases

You can connect to your local/hosted NoSQL database, which supports JDBC driver using the method specified here.

Connecting to Apache Cassandra

The following table consists of the default connection settings for the Apache Cassandra database.

DatabaseDBTYPEDriver Class NameConnection URLPort (default)
Apache Cassandracassandraorg.apache.cassandra.
cql.jdbc.CassandraDriver
jdbc:cassandra://<hostname>:
<port>/<keyspacename>
9160

Connecting to NoSQL databases through HIVE 

The following table consists of the default setting for connecting the Upload tool to various NoSQL databases using HIVE JDBC driver.

DatabaseDBTYPEDriver Class NameConnection URLPort (default)
Apache Hadoop HiveapachehiveHiveServer JDBC Driver: org.apache.hadoop.
hive.jdbc.HiveDriver

HiveServer2 JDBC Driver: org.apache.hive.jdbc.HiveDriver

HiveServer Connection URL: jdbc:hive://<hostname>:<port>/<dbname>

HiveServer2 Connection URL: jdbc:hive2://<host>:<port>/<dbname>

10000
Cloudera Hadoop HiveclouderahiveHiveServer JDBC Driver: org.apache.hadoop.
hive.jdbc.HiveDriver

HiveServer2 JDBC Driver: org.apache.hive.jdbc.HiveDriver

HiveServer Connection URL: jdbc:hive://<hostname>:<port>/<dbname>

HiveServer2 Connection URL: jdbc:hive2://<host>:<port>/<dbname>

10000
Hortonworks Hadoop HivehortonworkshiveHiveServer JDBC Driver: org.apache.hadoop.
hive.jdbc.HiveDriver

HiveServer2 JDBC Driver: org.apache.hive.jdbc.HiveDriver

HiveServer Connection URL: jdbc:hive://<hostname>:<port>/<dbname>

HiveServer2 Connection URL: jdbc:hive2://<host>:<port>/<dbname>

10000

Database SQL Queries

The Database sql queries file, namely database_sql_queries.xml, allows you to specify the SQL SELECT queries that are to be used to fetch the data from your local/hosted JDBC supported database and upload the same into Zoho Reports reporting database. You can specify any number of queries to fetch to upload data as required.

The following is the query format.

<Query id="" dbname="ReportsDBName1" tablename="ReportsTableName1"  importtype="UPDATEADD" matchingcols="Date, Customer Name" selectcols="" skiptop="" batchsize="10000" queuesize="">
  select * from local_db_table

</Query>

In the above query format the sample values for the parameters are given in bold. You can replace these bold content with your values. The following table explains parameters for the sql queries.

sql_query

Specify the SQL SELECT query to be executed in the local/hosted MySQL database for fetching the necessary data.

Example 1:

select * from local_db_table

This query fetches all the record from the local_db_table

Example 2:

select * from employee where age  &gt; 25

This query fetches all the record from the employee table whose age is greater than 25

Please note that the '<' symbol in the criteria should be replaced with &lt; and '>' symbol in the criteria should be replaced with &gt;

dbname

The Zoho Reports database name into which the data is to be uploaded after executing the SQL Query.

Note: Ensure that the reporting database is already available in Zoho Reports account. If it does not exist create the database before executing this upload to avoid failure. Refer Creating a New Reporting Database to know how to create a reporting database.

tablename

The Zoho Reports table name into which the data is to be uploaded after executing the SQL Query.

Ensure that the specified table with similar column structure is already created in the Zoho Reports Reporting Database. Refer Creating a Table to know how to create a table.

You also allow Zoho Reports to create the table by setting the ZOHO_CREATE_TABLE parameter to true in the common_params.conf file.

importtype

Sets how the data need to be imported.

Available options are :

  • 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 the common_params.conf of the conf directory.
  • TRUNCATEADD - Deletes the existing data and adds new data.
matchingcols

This is applicable only when the importtype is set to UPDATEADD. Specify column ( or a combination of columns) whose value will uniquely identify each record in that table. If the record already exists in the table then it will be replaced with the new values in the data being uploaded. Otherwise the data will be added at the end of the table.

selectcols

The column names separated by comma. Only these columns are uploaded from the resultant query data into the online reporting database.

Leave this as "" (empty) if you want all the columns to be uploaded.

skiptop

The number of rows to be skipped from the top in the resultant query data before being uploaded. Leave this as "" (empty) if you want all the rows from the resultant query data to be uploaded.

batchsize

Upload Tool splits the data into batches for uploading. The batchsize param splits the huge data into batches for uploading. The default value for the batchsize param is 10000.

This is a replacement for "LINES_TO_SEND" parameter which can be found in common_params.conf file. The param "LINES_TO_SEND" is deprecated in the latest version of the upload tool.

queuesize

Upload Tool splits the data into batches for uploading and writes the data in the temporary local file. The queuesize helps to regulate the file generations. Default value is nill (which means, there is no limit in file generation).

Executing the Upload Tool

Once you have configured the parameters, you can execute the Upload Tool by invoking the below file available in the bin folder.

  • In Windows OS - UploadFromDB.bat
  • Mac/Linux OS - UploadFromDB.sh

You can also execute the Upload Tool using the commamd line.

The following is an example command to invoke the Upload Tool.

UploadFromDB.bat

You can also specify the authentication details such as Zoho Reports Login e-mail ID, AuthToken and Database owner e-mail ID in the command line. However, specifying these 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.

The following is an example command to invoke the Upload Tool along with user e-mail ID and AuthToken.

UploadFromDB.bat <zoho_login_email_address> -A <authtoken>

The following is an example command to invoke the Upload Tool along with user e-mail ID, AuthToken and Database owner e-mail ID for shared users.

UploadFromDB.bat <zoho_login_email_address> -A <authtoken> -D <database_owner_login_e-mail_address>

Note Mac users:

Mac users need to define the following settings in the setEnv.sh file of the Upload Tool's /bin folder. The upload tool that you download will be same as that of the Linux version. You need to make the following changes before using the tool.
  • After you unzip the Upload Tool files, open a terminal/command-line and change the directory to /bin.
  • Type the following command at the command prompt.
    whereis java
  • Copy the java location returned by your system.
  • Open the setEnv.sh file and remove the following snippet.

    export JAVA_HOME=$TOOL_HOME/jre

  • Add the following snippet.
    export JAVABIN=<paste_your_mac_os_java_location>
    Example
    export JAVABIN=/usr/bin/java
  • Save the setEnv.sh file.

Setting up Periodic Upload / Synchronization

Using Upload Tool you can periodically schedule data uploads from your local/hosted JDBC supported database to Zoho Reports. This enables you to have the latest data from your application synced peridoically into Zoho Reports, and hence reports created over this data stays current.

You can setup a periodic scheduler using the Operating System Scheduler feature as explained below.

Setting up Schedule in Windows Operating System

  1. Click Start > Settings > Control Panel >Scheduled Tasks.
  2. Click Add Scheduled Task. The Schedule Task wizard will open.
  3. Click Action > Create Task to open the Create Task dialog.
  4. Open Action tab and then click New.
  5. Click Browse button and select the upload tool command line batch file UploadFromDB.bat in the Program/Script field. Ensure that you have provided the necessary settings in the Upload tool configuration files.
  6. In the Add Argument field enter the following command line arguments.
    <zoho_login_email_address> -A <authtoken>

    Example

    eduardo@zillum.com -A bad18eba1ff45jk7858b8ae88a77fa30

  7. Specify the time to trigger the task in Trigger tab.
  8. Click OK to save the task.

Setting up Schedule in Linux or Mac Operating System

In Linux and Mac, you can use the crontab command for scheduling the migration process using the command line script UploadFromDB.sh (Checkout Simple Help on Linux Crontab command)

The following steps explains how to setup the cron utility for scheduling data upload at a specific interval.

  1. Open the terminal and type the below command.
    crontab -e
  2. Append the prarmeters as a command in the following format :
    MIN HOURS * * * /UploadToolHome/bin/UploadFromDB.sh username -A authtoken
    Note: * operator is used to specify all possible values for a field.
    Example
    30 8 * * * /UploadToolHome/bin/UploadFromDB.sh eduardo@zillum.com -A bad18eba1ff45jk7858b8ae88a77fa30
    The above script will execute the Upload Tool at 8:30 AM everyday and upload the data from your JDBC supported database.
  3. Save the file.

Syntax of crontab

The following are the crontab syntax used to schedule Upload Tool.

MIN HOUR DOM MON DOW CMD ARG1 ARG2 ..

  • MIN: Minutes (0 to 59)
  • HOUR: Hours (0 to 23)
  • DOM: Day of Month (1 to 31)
  • MON: Month Field (1 to 12)
  • DOW: Day of Week (0 to 6)
  • CMD: Command to execute. For scheduling data upload, specify the absolute path of UploadFromDB.sh file
  • ARG: Arguments/Parameter will pass to script. It is an optional.

Click here to know more about the cron jobs.

Points to consider, while scheduling periodic upload using Command line mode:

  • It is the users responsibility to ensure that the latest data is uploaded into Zoho Reports by setting the schedule interval accordingly . Also ensure that you have provided the appropriate SQL Select query to pull the data.
  • In case you have deleted few records in the tables in your local database being uploaded, the only option to remove these records from Zoho Reports database is to set the Import Type as TRUNCATEADD. This will delete all the records in the corresponding table in Zoho Reports and then add the data newly fetched records from the local database into Zoho Reports.
  • In case you have modified few records in the tables in your local database being uploaded, then to get this modified in Zoho Reports database set the Import Type as UPDATEADD and specify the matching columns. The Upload Tool will compare the records in the corresponding table in Zoho Reports with the data being uploaded from your local database table based on the matching columns. If the record already exists in the Zoho Reports table then it will be replaced with the new values available from the local database. If not, it will be added as new records in Zoho Reports.

Viewing the Data Online

To view the data that you have uploaded:

  1. Login to http://reports.zoho.com
  2. Click on the corresponding Database name under My Databases
  3. Click on the corresponding table at the Left to open the table and view the uploaded data.

FAQ and Troubleshooting Tips

Frequently Asked Questions

1. Can I use my Yahoo / Gmail / Google Apps / Facebook user credentials to upload data using Upload Tool?

2. How to increase / decrease the batch size for upload?

3. Can I allow the shared user to upload the data into my table?

4. Can Upload tool be used to upload data from remotely hosted databases (Mongodb, Apache Cassandra, Apache Hadoop Hive, Cloudera Hadoop Hive and Hortonworks Hadoop Hive) into Zoho Reports?

5. Is it possible to fetch data from multiple database using Upload Tool?

6. Is it mandatory to follow similar column names in Zoho Reports table as available in my local database?

7. How to upload data with different date format from the in-house/local databases into Zoho Reports?

Troubleshooting Tips

Problem 1: I get "The host did not accept the connection within timeout of 15000 ms" while trying to upload data using Upload Tool. How to solve it?

Problem 2: I get an error message as ‘out of memory’ while trying to upload the file. How can I solve this error?

Problem 3: I get "Error!!! Sorry, you cannot upload files that exceed 50MB in size" while uploading data using Upload Tool. How to overcome this?

Problem 4: I get an error message as "Another import is in progress in this table started by the user 'User name' at 'time of import'." How to overcome this?

Problem 5: I get a message as "ZOHO_MATCHING_COLUMNS is not present in the request parameters list". How to solve it?

Problem 6: I get "Error!!! Column "Column_Name" is present in match columns but not in selected columns" while uploading data. How to overcome this?

Problem 7: I get an error message as "Maximum Concurrent User Tickets Limit Exceeded". How to solve this?