Uploading Data from MongoDB
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 NoSQL databases behind firewall into Zoho Reports. If you have stored your data in MongoDB database, 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 your local/hosted MongoDB 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.
Zoho Reports Upload Tool can be downloaded from the link below.
You can easily install the tool by extracting the file in the required location.
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 MongoDB database from which data need to be uploaded.
- database_sql_queries.xml - Allows you to specify the queries that are to be used to fetch the data from your local/hosted MongoDB database and upload the same into Zoho Reports reporting database.
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.
Specify your Zoho Reports account E-mail ID
|AUTHTOKEN||Specify 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.
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
Specify the Zoho Reports account Admin e-mail ID here.
To connect the tool to Zoho Reports server through a proxy server, you need to provide the following. If you are using a Direct Internet connection, then this setting can be ignored.
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.
|PROXYHOST||Specify the machine name or IP address where the proxy server is running in your network.|
Specify the port in which the proxy server is running.
Specify your proxy server user name to access the proxy server.
Specify your proxy password to access the proxy server.
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.
You can configure the settings to connect to your local/hosted MongoDB database in the database_connection_params.conf file.
Specify the following connection parameters.
|DBTYPE||Specify the database type as mongodb.|
Specify your local/hosted machine name in which MongoDB is running.
|PORT||Specify the database port number. The default port number for MongoDB is 27017.|
|USERNAME||Specify the MongoDB user name.|
|PASSWORD||Specify the MongoDB password.|
|DBNAME||Specify the local MongoDB database name from which the data is to be uploaded.|
The database queries file, namely database_sql_queries.xml, allows you to specify the MongoDB Queries that are to be used to fetch the data from your MongoDB database and upload the same into Zoho Reports reporting database. You can specify any number of queries to fetch to upload data as required.
By default, MongoDB Query node will be commented. You can uncomment <Query> node for MongoDB and comment or delete the <Query> node for SQL Queries.
The following is the MongoDB query format.
<Query dbname="zoho_reports_dbname" tablename="zoho_reports_tablename" importtype="APPEND / TRUNCATEADD / UPDATEADD" matchingcols="colums to match in case of UPDATEADD" selectcols="columns to be uploaded" skiptop="">
<FieldNames>Field names separated by commas</FieldNames>
<FieldAliasMapping>Field alias names as fieldname : field alias name separated by commas</FieldAliasMapping>
<Find>find query to fetch the required documents</Find>
In the above query format you can replace the bold content with your values. The following table explains parameters for the queries that need to be filled.
Specify the MongoDB Query to be executed in the local/hosted MongoDB database for fetching the necessary data.
<FieldNames>name.firstname, name.lastname, dob, address.street, address.city, address.state</FieldNames>
This query will fetch the data as follows:
<FieldNames>name.firstname, name.lastname, dob, address.street, address.city, address.state</FieldNames>
The above query will import all the data from the collection into the mytable.
The following data types are not supported and these types will be imported as empty / null into Zoho Reports. Aggregation framework are also not supported.
The Zoho Reports database name into which the data is to be uploaded after executing the 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.
The Zoho Reports table name into which the data is to be uploaded after executing the 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.
Sets how the data need to be imported.
Available options are :
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.
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.
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.
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.
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).
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 command line. The following is an example command to invoke the Upload Tool.
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.
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.
Note Mac users:
- 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.
- Copy the java location returned by your system.
- Open the setEnv.sh file and remove the following snippet.
- Add the following snippet.
- Save the setEnv.sh file.
Using Upload Tool you can periodically schedule data uploads from your local/hosted MongoDB 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.
- Click Start > Settings > Control Panel >Scheduled Tasks.
- Click Add Scheduled Task. The Schedule Task wizard will open.
- Click Action > Create Task to open the Create Task dialog.
- Open Action tab and then click New.
- 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.
- In the Add Argument field enter the following command line arguments.<zoho_login_email_address> -A <authtoken>
Exampleeduardo@zillum.com -A bad18eba1ff45jk7858b8ae88a77fa30
- Specify the time to trigger the task in Trigger tab.
- Click OK to save the task.
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.
- Open the terminal and type the below command.crontab -e
- Append the prarmeters as a command in the following format :MIN HOURS * * * /UploadToolHome/bin/UploadFromDB.sh username -A authtokenNote: * operator is used to specify all possible values for a field.Example30 8 * * * /UploadToolHome/bin/UploadFromDB.sh email@example.com -A bad18eba1ff45jk7858b8ae88a77fa30The above script will execute the Upload Tool at 8:30 AM everyday and upload the data from your MongoDB database.
- 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 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:
- Login to http://reports.zoho.com
- Click on the corresponding Database name under My Databases
- Click on the corresponding table at the Left to open the table and view the uploaded data.
Frequently Asked Questions
1. Can I use my Yahoo / Gmail / Google Apps / Facebook user credentials to upload data using Upload Tool?
Currently, Zoho Reports Upload Tool does not support uploading data using Yahoo / Gmail / Google Apps / Facebook accounts credentials. The Upload Tool expects Zoho Username and Password to authenticate the user. So other login users need to generate a Zoho Username and Password to upload data into Zoho Reports database.
Follow the steps below to generate a Zoho Username and Password:
- Login into Zoho Reports using your Yahoo / Gmail / Google Apps / Facebook account credentials.
- Click the drop-down menu displayed with your user name at the top right corner.
- Click Account Settings menu item, this will open Zoho Accounts Profile page (You can also directly access/open this page using direct link https://accounts.zoho.com).
- In the Home tab, click Change Password link.
- Reset the password.
Now in Zoho Reports home page, you can find the Zoho Username at the top right corner. Use the new Zoho Username and Zoho Password in the Zoho Reports Upload Tool to upload the data into your account.
- Generating Zoho password will not affect signing into your account using your Yahoo / Gmail / Google Apps / Facebook account.
To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads as batches. The user can configure the number of rows to be sent for each batch by modifying the LINES_TO_SEND parameter in the common_params.conf file. By default this will be set to 5000.
Follow the steps given below to specify the batch size:
- Open /conf/common_params.conf file.
- Set the LINES_TO_SEND parameter to the number of lines you want to upload in each batch as given below.
- Save the configuration file and start uploading the data.
- It is mandatory that the each batch size should not exceed 20 MB or 100,000 records.
3. Can I allow the shared user to upload the data into my table?
Yes, you can allow your shared users to upload data into your tables using Upload Tool. Follow the steps given below to do this.
Steps to be followed by you who is the Admin or Database Owner:
- Login into database owner's account.
- Share the table, in which you want to allow your shared user to upload data, with Import Permission.
- For more details, refer to this FAQ section.
Steps to be followed by the shared user:
- Open /conf/common_params.conf file.
- Add the DBOWNERNAME parameter and set this to Database Owner's Zoho Reports Email id or User name
- Save the configuration file.
- Start uploading the data using the shared user's Zoho Reports credentials.
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?
Yes, you can upload data from remotely hosted databases (running in a remote machine) into Zoho Reports using Upload Tool, provided a network connection via Java Database Connectivity (JDBC) Driver could be established between the remote database server and the machine in which you have installed the Upload Tool.
The connection setting for the hosted database should be done in the same way as you do for a local database. For the "HOSTNAME" and "PORT" parameters, you need to specify the remote hosted database server IP address or host name and the corresponding port number. To know how to specify connection setting, refer to Specify Database Connection Settings section.
5. Is it possible to fetch data from multiple database using Upload Tool?
Yes, it is possible to setup the Zoho Reports upload tool to fetch data from multiple database. By default, the Upload tool is designed to upload data from one database in an upload process. However, we can modify the configuration files and executable bat or sh files to upload data from more than one database.
To accomplish the above set up, you need to create different set of configuration files required for each database.
For example, let's say you have two different databases to be connected for data upload. We should have one set of configuration files for Database One and another set of configuration files for Database Two.
Follow the below steps once you have downloaded the sample and extracted the same.
- Conf files to be copied to Upload tool's config folder
- Executable "UploadFromDB.bat" file to be copied to Upload tool's bin folder
( i.e., "<UPLOAD_TOOL_HOME>ZohoReports/UploadTool/bin"). The .bat file has been modified to upload the two different databases with the given two different configurations.
The following are the steps to configure two databases in the Upload tool:
Configuration for Database One:
- Provide the Database One connection properties in
- Define the necessary queries in " database_sql_queries1.xml ".
Configuration2 for Database Two:
- Provide the Database Two connection params in
" database_connections_params2.conf ",
- Define the necessary queries in " database_sql_queries2.xml ".
Once the above setup is done, execute the modified batch files copied to the bin directory. This will fetch data from both the databases and upload that into Zoho Reports.
6. Is it mandatory to follow similar column names in Zoho Reports table as available in my local database?
No. You can follow a different column names in Zoho Reports table from what is there in your local database and still upload data using Upload Tool. Follow the steps to upload the data from your local database into the corresponding column in Zoho Reports table.
- Open <Tool_Home>/conf/database_sql_queries.xml.
- Specify the query as given below to upload data from local database into the corresponding column in Zoho Reports table.
The above query will pull the data from column1-local-db, column2-local-db andcolumn2-local-db from your local database databasetable1 and import them into the column incolumn1-zohoreports-table, column1-zohoreports-table and column1-zohoreports-table in the Zoho Reports table.
- Save the file and start the upload process.
7. How to upload data with different date format from the in-house/local databases into Zoho Reports?
Zoho Reports Upload Tool expect the date format of the data being uploaded as dd/MM/yyyy HH:mm:ss. In case you are using a custom date format in your local database, then you need to convert your date value to the required format in your Query using the DATE_FORMAT function.
To convert the data format you need to set a date column to the DATE_FORMAT function and specify the required default date format as given below.
In case your date column is set as string field, then you need to convert the data type using DATE function as given below.
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?
Solution: This could be because of improper proxy server settings. If you are connecting the Internet through a proxy server, ensure that you have configured the correct proxy server details in the common_params.conf file. For more details, refer to the Setup: Common Settings and Proxy Configuration section.
Problem 2: I get an error message as ‘out of memory’ while trying to upload the file. How can I solve this error?
Solution: This could be because, the default memory allocated to the Java Virtual Machine (which executes the Upload tool) in your machine was not sufficient to import a large file using our Upload Too. Hence it throws the "Out of Memory". Follow the given steps to resolve this issue:
- Open the file setEnv.bat / setEnv.sh available in the directory <Upload_Tool_Home>/bin
- Add the property -Xmx1024 at the end of the variable JAVA_OPTS as below:
Windows: set JAVA_OPTS=%JAVA_OPTS% -XX:NewSize=48M -Xmx1024M
Linux/Mac: export JAVA_OPTS=$JAVA_OPTS -XX:NewSize=48M -Xmx1024M
- Save the file and start the upload process.
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?
Solution: To upload the data of larger size, Zoho Reports Upload Tool splits the data and uploads them as batches. It is important that the batch size should not exceed 20 MB or 100,000 records per batch. You can increase or decrease the lines/records to send for each batch in common_params.conf file.
Follow the steps given below to set the lines to be uploaded in a batch:
- Open <Tool_Home>/conf/common_params.conf file
- Change the parameter LINES_TO_SEND=<no_of_lines>
- Save the configuration file and start uploading the data
For more details, refer to #Q3.
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?
Solution: You will get the above message when more than one import process is running on the same table at the same time. Ensure that no other user is importing into the same table before initiating the progress.
Problem 5: I get a message as "ZOHO_MATCHING_COLUMNS is not present in the request parameters list". How to solve it?
Solution: You will get the above error message when you have set the import type to UPDATEADD and have not specified any value for the ZOHO_MATCHING_COLUMNS parameter. This parameter is mandatory when the ZOHO_IMPORT_TYPE is set to UPDATEADD. Set this parameter to column names based on which the existing records in the table need to be matched. If the record already exists in the table then it will be replaced with the new values in the uploaded CSV file. Remaining rows will be added at the end of the table.
- It is recommended to set columns with unique values as matching column.
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?
Solution: You will get the above error when the column specified for the ZOHO_MATCHING_COLUMNS parameter is not available in the table. When you have set Import Type to UPDATEADD, it is mandatory to specify matching column. These columns will be used to check whether a record already exist in the table. Ensure that you have specified a valid column name for matching column before uploading data to avoid failure.
Problem 7: I get an error message as "Maximum Concurrent User Tickets Limit Exceeded". How to solve this?
Solution: When you access the service a session will be created, which will be deactivated once you logout or after 7 days. If you did not logout or access the service from multiple locations then multiple active sessions will be created for your account. Zoho service restricts a user to have maximum of 20 active sessions. In case you have exceeded this limit, you will not be allowed the access the service. To overcome this you need to close the active sessions.
Follow the steps below to close the current active sessions.
- Login to http://accounts.zoho.com with your Zoho account credentials
- Choose the Active Session under the Home tab.
- Remove all active sessions by clicking the close all other sessions.
Uploading data from CSV file
Problem 1: The length of the column names are not more than 100 characters, but receiving the message: "Sorry, Column name should not exceed 100 characters". How to solve this?
Solution: This could be because the Column Delimiters (delimiter character which separates one column from another in the data ) was identified incorrectly. Follow the steps below to resolve this issue:
- Open the common_params.conf file available in <Tool_Home>/conf directory.
- Set the ZOHO_AUTO_IDENTIFY parameter to false.
- Add the following parameters and set them to appropriate delimiter:
- ZOHO_DELIMITER (Specify anyone of the delimiter character, based on how the columns are delimited in your data. Where 0 is COMMA, 1 is TAB, 2 is SEMICOLON and 3 is SPACE. )
- ZOHO_QUOTED (Specify anyone of the value based on whether text/string values in your data are quoted by any character. Where 0 is NONE, 1 is SINGLE QUOTE and 2 is DOUBLE QUOTES)
- Save the common_params.conf file and start the upload process.
For more details on these parameters, click here.