Tagged with database_import_blocks - Visokio Forums

Sources: KDB+ Database

chris — Fri, 30 Jan 2015 12:15:14 -0500

Hi,

Some of our clients have asked whether Omniscope supports reading/writing data to KDB+ (see http://kx.com/software.php).

KDB is not currently on our list of supported databases, however it is still possible to connect and pull in data in Omniscope, here's how:

1. Start KDB, specifying the port we want the webserver to listen on, for example on windows to start KDB on port 5000:

q -p 5000

2. Download the KDBC JDBC driver. This can be downloaded from:

http://kx.com/q/c/jdbc.jar

3. Open Omniscope, drag in a "Database table" block from the sources.

4. In "Select database" choose "JDBC (Advanced)".

5. In "Driver jar path" select the JDBC driver downloaded in step 2.

6. In "Driver class:" enter "jdbc".

7. In "JDBC URL" enter "jdbc:q:localhost:5000". If we are running KDB on a different port change the url.

8. Enter the username/password (if configured).

9 Click the "Connect" button. You should now be able to select a table and download the data, incorporating it into your DataManager workflow.

I've attached a screenshot with a configuration I was successfully able to use to connect and download 1 million records into Omniscope. This runs in a few seconds with the sample data I tested.

In the next few days we intend to add "KDB" to the list of supported databases, so you can use this instead of having to select the "JDBC (Advanced)" option. I'll post back once this has been implemented.

In the meantime please let us know if you encounter any problems using the method described above.

Chris

Linux Headless Server/Scheduler - Unable to read data from database

Veaceslav — Wed, 01 Oct 2014 14:04:01 -0400

This post describes how to check and fix Unable to read data from database errors on a headless Omniscope server or scheduler running on Linux.

If you can run Omniscope Desktop on your machine, you don't need to follow these instructions. Instead, you should run Omniscope Desktop, open your file, go to Data Manager and expand your database block. A dialog should be displayed asking you to locate your database driver. Select the jar file, re-load your data and save the file.

Note: Database (jar file) drivers should be downloaded from the official website distributing the driver.

If you cannot run Omniscope Desktop on your machine to configure the driver from the UI (e.g. you have a headless Linux machine) then you should follow the next steps:

1. Check whether you need to specify a driver location

If you already know that the driver location is wrong or missing, go to step 2.
If you get only 'Error Unable to read data from database' without any additional details, you need to check whether the driver location is the root cause of the problem.

a. Edit {OMNISCOPE_INSTALL_DIR}/_launch.sh
```
    {OMNISCOPE_INSTALL_DIR} is the directory where Omniscope is installed
```
Change JAVA_VM_PARS property to include both AppSetting_APPSETTING_REPORT_RECOGNISED_ERRORS_AS_BUGS and AppSetting_APPSETTING_REPORT_DATABASE_ERRORS_AS_BUG, leave all other JVM flags unchanged.

E.g.
JAVA_VM_PARS='-Xms64M -Xmx1100m -XX:MaxPermSize=128M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=50 -XX:NewRatio=8 -XX:+UseG1GC -DAppSetting_APPSETTING_REPORT_RECOGNISED_ERRORS_AS_BUGS=true -DAppSetting_APPSETTING_REPORT_DATABASE_ERRORS_AS_BUGS=true'

b. Run your tasks again
At this point you should get more details about the error and it should look like this:

...driver JAR file location not specified. Please reconfigure this legacy file's database connection in the desktop application.

2. Configure Omniscope to use a specific driver
My example shows how to configure Omniscope to use a MySQL driver. Other database types are very similar.

Download MySQL JDBC driver from MySQL website.
E.g. http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.33.zip
Unzip, get the jar file and put it somewhere on the machine running Omniscope scheduler
E.g. /home/visokio/visokio-omniscope-2.9-b1200/lib/mysql-connector-java-5.1.33-bin.jar
Edit {OMNISCOPE_INSTALL_DIR}/_launch.sh again
Modify JAVA_VM_PARS property to include the JDBC driver location and remove the flags added at step 1.(a)
E.g.
JAVA_VM_PARS='-Xms64M -Xmx1100m -XX:MaxPermSize=128M -XX:MinHeapFreeRatio=20 -XX:MaxHeapFreeRatio=50 -XX:NewRatio=8 -XX:+UseG1GC -DMySqlDriverSearch_driverDir=/home/visokio/visokio-omniscope-b620/lib/mysql-connector-java-5.1.33-bin.jar'

Note: Here are property names for the other databases (make sure you prepend a '-D' in front of the property e.g -DMySqlDriverSearch_driverDir):

IngresSqlDriverSearch_driverDir
MySqlDriverSearch_driverDir
OracleThinDriverSearch_jarFile
PostgreSqlDriverSearch_driverDir
SqlServer2008DriverSearch_jarFile

These steps configured Omniscope scheduler/server to locate and use database drivers when running in headless mode.

Note: If you configure your file on one machine, then use it on another machine, the driver needs to be added manually on the second machine, otherwise you will get the 'Unable to read data from database' error.

Performance: Refreshing heavy views from database?

Alfonso — Tue, 16 Apr 2013 12:56:31 -0400

Hi,

In the refreshing process, we have issues updating from the database. The issues happens in heavy queries or heavy views, resulting in a no coherent model at user interface level.

Could be the asking database time the cause. Is there experiences managing the updating interval for solving this kind of issues?.

Thanks in advance, Alfonso

Sources: Dynamic Data Source names?

jshedley — Mon, 04 Mar 2013 02:32:11 -0500

Hi - Is it possible to have dynamic data sources? We have a system that has a new database created each month and we want to put this in without having to open Omniscope and manually edit the file.

Thanks, Jamie

DataManager: Database import block SQL error?

bfromson1 — Tue, 18 Dec 2012 07:05:32 -0500

I tried to set up filters in a database import block for a text field "does not contain" a text string.

1) The query returned no records, as the SQL generated was looking for it to contain the text.

2) And while clicking between the "Configuration" "Filters" and "View SQL" tabs it has lost my field selections and gone back to select all

3) Minor UI issue, if you expand the View SQL box horizontally the text wrap extends out so that the SQL string extends across the available view. If you then make the view smaller horizontally it doesn't re-wrap the text but puts a scroll bar on the horizontal axis of box - minor problem, but what's wrong with perfection!

Sources: Remote database connectivity network error?

jgaynor — Tue, 18 Dec 2012 18:41:10 -0500

We have a new ISP and received the following error messages:

Database error
An error occurred connecting to or querying the database.
Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.

Detailed information
Network error: A network error has occurred. Please check your connection and try again.
Connection timed out: connect

I don't recall having to provide any IP or ISP information when we installed Omniscope.

Is there something we needed to do?

DataManager: Automatically import/export database tables?

Alfonso — Tue, 09 Oct 2012 06:39:56 -0400

I'm trying to use Omniscope DataManager ETL workspace data flows to create a tool for importing several tables into a Database Structure as an automated process. It does not seem possible to manage this import and output sequence automatically?

I defined a data flow like this: Open database -> organize fields -> validate data -> publish data using a database table output only. My data flow does not include a load into the blue ouput block representing the in-memory DataExplorer output for visualisation/presentation/reporting. Could we have the ability to launch this type of ETL data flow in batch mode (silent or no prompts doesn't allow the publish process run automatically)? Please if you have any ideas, let me know. - Alfonso

Macs: JTDS Error

doug_ldc — Mon, 02 Jul 2012 06:52:38 -0400

Hi - I've been getting this error for a while now when trying to connect to a SQL server using the jTDS plugin. do you know how I can reinstall the driver on Mac OSX running Lion 10.7.4
Thanks Doug

DataManager: Database source field picker (2.8+)

chris — Wed, 11 Apr 2012 13:06:29 -0400

In Omniscope 2.8 we have now added a field picker to the Database configuration options. This allows you to select which fields to import without having to customise the SQL statement.