Uploading data from relational databases

Uploading Data from Relational Databases

  • Configure Local Database Connection Settings
  • Specify SQL SELECT Queries to be executed on local database
  • Configure Import Settings
  • Executing the Analytics Plus agent
  • Setting up Periodic Upload / Synchronization
  • Viewing the data in the application
If you have any applications that stores data in any of the local databases like MySQL, SQL Server, Sybase, PostgreSQL, Oracle, SQLite, Firebird or any other database that runs behind the firewall (i.e in your local network),  you can use Analytics Plus agent to pull data from your databases and upload the same into ManageEngine Analytics Plus. In this section, we will discuss how to upload data from your local databases into ManageEngine Analytics Plus. You can also read about how to configure the Analytics Plus agent to periodically upload/synchronize the data from your local databases into ManageEngine Analytics Plus. 

Configure Local Database Connection Settings

To upload data from local (in-house) databases, configure the proxy settings (if your network has a proxy server setup)  in the common_params. conf file, and then specify your local database connection settings in the configuration file database_connection_params.conf as described below.

Specify Local Database Connection Settings

 You need to specify the connection setting about your in-house database behind firewall in this file. The following screenshot illustrates a typical database_connection_params.conf.

The following are the parameters that are to be given in database_connection_params.conf file: 

DBTYPE 

Specify your local database type. e.g: MySQL, SQL Server, Oracle, Sybase etc.,. 

HOSTNAME  

Host name of your computer in which the database is running, i.e., the name of the computer where your database is running.

PORT

The port number used by the database server.  

USERNAME

Specify the local database user_name.

PASSWORD

Specify the local database password.

DBNAME 

Your local database name from which the data is to be uploaded.

MDBFILENAME

In case you are going to upload data from MS Access database, then specify the complete path of MS Access database file name which contains the data. 

e.g., MDBFILENAME=C:\\MSACCESS\\mdbfiles\\test.mdb.

The example in the above screenshot illustrates the connection settings for a MySQL database. You can fill similar values for Microsoft SQL Server, Sybase and Oracle.

Default Settings for different Databases

The following table captures the default connection settings that can be specified in the database_connections_params.conf for different database vendor type. If you are not using the default port provided by the database, then you need to specify the corresponding port for connecting.

DatabaseDBTYPEPORT (default)MDBFILENAMESQLITEFILENAME
MySQLmysql3306--
Oracleoracle1521--
SQL Serversqlserver1433--
Sybasesybase5000--
PostgreSQLpostgresql5432--
SQLitesqlite--Example:
C:\\SQLITE\\sqlitefiles\\Sales-Database.db 

Setting up JDBC Driver for MySQL

If you plan to use the Analytics Plus agent to fetch data from your local MySQL databases and upload it into ManageEngine Analytics Plus, then you need to setup the MySQL JDBC driver for the agent to work. For all other supported databases the corresponding JDBC drivers are bundled along with the agent, by default.

The following are the steps to setup the MySQL JDBC driver: 

  • Extract the downloaded file (using tools like Winzip, tar etc.,).
  • Copy the JDBC driver file "mysql-connector-java-3.0.17-ga-bin.jar" into the Analytics Plus agent directory <Agent home>\lib.

Once the JDBC driver is set, you can connect the Analytics Plus agent to any local MySQL database to fetch the required records and upload the same into ManageEngine Analytics Plus. 

Using SQL Server Windows Authentication

Windows Authentication can also be used to connect to your local SQL Server database. To do this, you need to provide the database name followed by the Windows domain name separated by semi-colon (  ; )  for the parameter DBNAME in the database_connections_params.conf file.

Syntax

DBNAME=<your_database_name>;domain=<your_domain_name>

Where

<your_database_name> - This is the database name in SQL Server to connect
<your_domain_name>    - The Windows domain name that you use.

Example: DBNAME=sqlserverdbname;domain=workgroup

Specify SQL SELECT Queries to be executed on local database

The database_sql_queries.xml file contains all the settings related to the SQL SELECT Queries that are to be executed in the in-house database for fetching the required data, along with settings about the ManageEngine Analytics Plus database and table names into which the data is to be uploaded. 

<Query dbname="manageengine_analyticsplus_dbname" tablename="manageengine_analyticsplus_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>

To fetch the data from your in-house database you need to provide the necessary SQL SELECT queries that are to be executed in the database. These queries will be executed by the Analytics Plus agent in your local database to fetch the necessary records and upload the same into ManageEngine Analytics Plus 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.

As part of the <Query> node, you also need the provide details about the ManageEngine Analytics Plus database into which the data that is fetched from executing the SQL SELECT query is to be uploaded.  

The following table explains all the parameters to be provided as part of the <Query> node.

ParameterDescription
dbnameThe ManageEngine Analytics Plus database name into which the data is to be uploaded after executing the SQL Query. 

Note: Ensure that the database is already available in ManageEngine Analytics Plus account. If it does not existcreate the database before executing this upload to avoid failure.

tablenameThe ManageEngine Analytics Plus 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 ManageEngine Analytics Plus workspace.

In case the table does not exist, then you can set the Analytics Plus agent to create the table and then upload the data into this table using the ZOHO_CREATE_TABLE parameter available in common_params.conf file. Refer to the Configure Import Settings section to know more about the ZOHO_CREATE_TABLE parameter.

importtypeSet 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.
matchingcolsThis 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 CSV file being uploaded.  Otherwise the data will be added at the end of the table.
selectedcolsThe column names separated by comma. Only these columns are uploaded from the resultant query data into the database.

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

skiptopThe 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. 

sql_querySpecify the SQL SELECT query to be executed in the local database for fetching the necessary data. 

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

Example: select * from employee where age &gt; 25

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

You can specify any number of SQL SELECT queries using the <Query> XML Element as described above. 

Configure Import Settings

The configuration file common_params.conf in /conf directory provides parameters that Analytics Plus agent uses while uploading data. These import settings help ManageEngine Analytics Plus 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 local 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.

ParameterPossible ValuesDescription
ZOHO_CREATE_TABLE
  • True
  • False

Optional
This parameter specifies whether to create the table or throw error, if the specified table does not exist in the database.

  • True - New table will be created in the specified database with the said name and then the data will be imported. If the table already exists in the database, then the data will be imported into it.
  • False - The data will be imported into the specified table. If the table does not exist in the specified database then it throws an error.

Default value is false.

LINES_TO_SEND 

Optional 
Analytics Plus agent splits the CSV file into batches for uploading. This parameter specifies the number of row for each batch.
Default value is 5000.

ZOHO_THOUSAND_SEPARATOR
  • 0
  • 1
  • 2
  • 3

Optional
This parameter specifies the thousand separator for the numeric data.
The following is the list of parameter values and the corresponding thousand separator.

  • 0 - COMMA
  • 1 - DOT
  • 2 - SPACE
  • 3 - SINGLE QUOTE

Default is 0.

ZOHO_DECIMAL_SEPARATOR
  • 0
  • 1

Optional 
This parameter specifies the decimal separator for the numeric data.

The following is the list of parameter values and the corresponding decimal separator.

  • 0 - DOT
  • 1 - COMMA

Default value is 0.

SET_EMPTY_FORNULL   
  • True
  • False
This parameter is used to specify whether empty value or null should be added in ManageEngine Analytics Plus table when the uploaded database contains null value. 
 
  • True -The null values in the local database server will be added as empty values in ManageEngine Analytics Plus table.
  • False - the null values in the local database server will be added as null in ManageEngine Analytics Plus table.

Default value is false.

ZOHO_CANOVERRIDE
  • True
  • False

Optional 

In case another import is triggered when this import is in process you can use this parameter to specify whether the new import can override this import. 

  • True - The agent will stop the current import process and overwrite the table with new import.
  • False - The agent will not disturb the current import process and will cancel the new import request.

Default value is false.

ZOHO_ON_IMPORT_ERROR
  • ABORT
  • SKIPROW
  • SETCOLUMNEMPTY
 

This parameter controls the action to be taken in case there is an error during import.

  • ABORT - In case of any error, the import will be aborted.
  • SKIPROW - In case of any error, the specific row(s) in which error occurred will be skipped and then continue importing the rest of the data.
  • SETCOLUMNEMPTY - In case of any error, the value of the specific cell(s) in which error occurred will be set to empty and then continue importing the rest of the data.  

Default value is SETCOLUMNEMPTY

Executing the Analytics Plus agent

Once you have configured the parameters, you can execute the Analytics Plus agent by invoking the below file available in the bin folder.

In Windows OS - UploadFromDB.bat
Linux OS - UploadFromDB.sh

You can also execute the Analytics Plus agent using the command line. The following is an example command to invoke the Analytics Plus agent. Specifying the User emaii id, Auth token, and the DB owner mail address (if it exists) 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.

  • UploadFromDB.bat "<hostname> <port_number>"

Run the UploadFromDB.bat / UploadFromDB.sh  file under <agent_Home>bin directory along with the required parameters. You have to provide ManageEngine Analytics Plus User Name and Password in the order shown below. 

  • UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress> <analyticsplus_password>"

If you have been made as a database owner and need to import data into that particular database, the <manageengineanalytics_dbowner_mailaddress> has to be specified in the following format:

  • UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress> <analyticsplus_password> -D <database_owner_login_e-mail_address>"

You can also specify the authentication details such as ManageEngine Analytics Plus Login e-mail ID, AuthToken and Database owner e-mail ID in the command line. The following is an example command to invoke the Analytics Plus agent along with user e-mail ID and AuthToken.

  • UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress>-A <authtoken>"

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

  • UploadFromDB.bat "<hostname> <port_number> <analyticsplus_emailaddress>-A <authtoken> -D <database_owner_login_e-mail_address>"

Note:

  • In case there is any failure in executing any of the given SQL Query, then the Analytics Plus agent will abort the execution of that query and the ones that follow. Whatever queries that had been successfully executed and uploaded into ManageEngine Analytics Plus till then will not be rolled back.

The details of the last upload executed will be logged in the file <Agent_Home>/logs/dbuploadserverout.txt

You can also execute this database upload periodically using a Scheduler to keep the data in ManageEngine Analytics Plus synchronized with your database. Read more in the next section.

Setting up Periodic Upload / Synchronization

Using Analytics Plus agent you can periodically schedule uploading data from a CSV file. With this mechanism, you can have the latest data from your application synced into ManageEngine Analytics Plus, 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. 

Setting up Schedule in Windows Operating System

  • 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 Analytics Plus agent command line batch file UploadFromDB.bat in the Program/Script field. Ensure that you have provided the necessary settings in the Analytics Plus agent configuration files. 
  • In the Add Argument field enter the following command line arguments.
UploadFromDB.bat <manageengine_analyticsplus_username> <manageengine_analyticsplus_password>  

Setting up Schedule in Linux Operating System:

  • ​In Linux, you can use the crontab command for scheduling the migration process using the command line script CSVUploadConsole.sh. (Checkout Simple Help on Linux Crontab command)
  • Ensure you have provided the necessary setting in the Analytics Plus agent configuration files. 

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 ManageEngine Analytics Plus 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 ManageEngine Analytics Plus database is to set the Import Typeas TRUNCATEADD. This will delete all the records in the corresponding  table in  ManageEngine Analytics Plus and then add the data newly fetched records from the local database into ManageEngine Analytics Plus.  
  • In case you have modified few records in the tables in your local database being uploaded, then to get this modified in ManageEngine Analytics Plus database set the Import Type as UPDATEADD and specify the matching columns.  The Analytics Plus agent will compare the records in the corresponding table in ManageEngine Analytics Plus with the data being uploaded from your local database table based on the matching columns. If the record already exists in the ManageEngine Analytics Plus 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 ManageEngine Analytics Plus.

Viewing the Uploaded Data

  • To view the data that you have uploaded,
  • Login to your ManageEngine Analytics Plus account.
  • 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.  

Share this post : FacebookTwitter

Can't find what you're looking for?

Write to us: analyticsplus-support@manageengine.com

Community: https://pitstop.manageengine.com/portal/community/analytics-plus