CData Virtuality

Find Enabled Recommended Optimizations Which Have No ScheduleHow-To

When enabling optimizations, you might want to make sure that each optimization has at least one schedule. This ensures that optimizations are refreshed regularly in order to return recent data.For verifying that every optimizations is covered by a schedule, you can run the following code which will return all optimizations that currently lack a schedule:SELECT ro.id ,ro.Matchdescriptor ,sj.description ,sd.* FROM "SYSADMIN.RecommendedOptimizations" ro left join "SYSADMIN.ScheduleJobs" sj on ro.id = sj.groupId left join "SYSADMIN.Schedules" sd on sj.ID = sd.jobID WHERE "ro"."Enabled" = true and sd.ID IS NULL;;

JohnCommunity Manager

Pass Key Value Pairs to a Procedure Using Multi-Dimensional ArraysHow-To

(A note: for passing variable number of parameteres into a stored procedure, you can also use the approach described here )To pass key value pairs to a procedure, you can use multi-dimensional arrays - here's an example:BEGINDECLARE object params = ARRAY( ARRAY( 'a', 'b') , ARRAY( 'c', 'd') );DECLARE integer VARIABLES.i = 0;CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" object, "value" object);WHILE( i < array_length(params)) BEGIN INSERT INTO "#__LOCAL__keyvalue_store" SELECT VARIABLES.i + 1 as "argument_number", params[i+1][1] AS "key", params[i+1][2] as "value"; VARIABLES.i = VARIABLES.i + 1;ENDSELECT * from "#__LOCAL__keyvalue_store";END CREATE VIRTUAL PROCEDURE views.parse_params( IN params object, IN "key" string )RETURNS("argument_number" integer,"key" string,"value" object)ASBEGINDECLARE integer VARIABLES.i = 0;CREATE LOCAL TEMPORARY TABLE "#__LOCAL__keyvalue_store" ( "argument_number" integer, "key" string, "value" object);WHIL

JohnCommunity Manager

The following error message "Streaming result set com.mysql.jdbc.RowDataDynamic@XXXXXX is still active. No statements may be issued when any streaming result sets are open and in use on a given connection. Ensure that you have called .close() on any active streaming result sets before attempting more queries" comes from MySQL.The reason for this lies in the streaming mode of MySQL. Streaming mode is the fastest way to get data from MySQL, and Data Virtuality uses this mode to transfer the data. However, streaming mode has one limitation: When a resultset is open in streaming mode on a MySQL connection, you cannot use that same database connection to run other queries. This is exactly what the error message above tells. The solution to the problem would be to change the MySQL connection settings to use CursorFetch mode instead of Streaming mode.To do this, you need to edit the MySQL connection in Data Virtuality to do the followingPress Edit on your MySQL datasource in DV Studio and:Set

Error message "Streaming result set com.mysql.jdbc.RowDataDynamic@XXXXXX is still active. ....."Troubleshooting

JohnCommunity Manager

Data Virtuality Suite contains a dedicated JDBC Driver in order to enable reporting tools and the Data Virtuality Studio to connect to the Data Virtuality Server using the JDBC protocol. Prior to using such BI tools, the Data Virtuality JDBC Driver must be installed. To install the JDBC Driver, simply put the datavirtuality-jdbc.jar file, which is downloadable from your data virtuality server, into a folder where the particular Reporting Tool can find it. To connect to Data Virtuality Server via JDBC make your BI / Reporting tool registering the supplied datavirtuality-jdbc.jar driver.For connecting use, for example, the following URLs:JDBC connection stringjdbc:datavirtuality:datavirtuality@mm://localhost:31000JDBC over SSL connection stringjdbc:datavirtuality:datavirtuality@mms://localhost:31001Example using the SQuirreL SQL ClientOpen Squirrel and press the '+' sign on drivers, or use Drivers → New Driver in the menu. Set a name for the driver (e.g. 'dv'). Enter example URL: jdbc:d

Connecting SQuirreL via JDBCHow-To

JohnCommunity Manager

This is a short guide on how to get data from Data Virtuality in Qlik.Please note that:Qlik uses Windows standard language settings; The ODBC- Driver Package from Data Virtuality must be installed up front; Qlik only works with the 64Bit ODBC connector. To connect Qlik via ODBC, please do the following:Download the Qlik application; Install and open Qlik; Create a new workspace (called APP in Qlik); Click on "Add new data":Choose ODBC:Use the 64 Bit ODBC driver; Enter your credentials:

Connecting Qlik via ODBCHow-To

JohnCommunity Manager

This is a short introduction on how to connect Data Virtuality Server to MS Excel via an ODBC connection. It is important to you use matching Excel and driver. For example, if you have installed the 64 Bit ODBC driver, then you need to have a 64 Bit Excel. Otherwise, it will not work.The Excel which was used to take the snapshots was in German.To connect Data Virtuality Server to MS Excel via ODBC, please do the following:Click "Data"; Click "Other source"; Click "Microsoft query" for example: Choose the correct ODBC driver and click "OK":

Connecting Excel via ODBCHow-To

JohnCommunity Manager

Motivation:This is a short example to demonstrate how to access Data Virtuality via an ODBC connection in F#. Please keep in mind that this is not an F#/.NET code and the sample just shows a basic approach for the ODBC connection.Requirements:The system which shall access Data Virtuality Server needs to have at least the ODBC drivers installed. A DSN connection is also possible but requires the ODBC drivers as well. They can be obtained from the web server which is implemented in Data Virtuality (see https://datavirtuality.zendesk.com/hc/en-us/articles/115002027906-Components-downloadable-from-DV-site) Simple code sample:The following sample creates a connection string based on a DSN. It is then used for a new ODBC connection and the program executes the query "SELECT * FROM dwh.foo" on this connection.Afterwards, the name of the first column of the result set is retrieved and all values for this column are printed. open Systemopen System.Data.Odbc [<EntryPoint>]let main argv =

Connecting F# via ODBCHow-To

JohnCommunity Manager

The following snippet can be used with Data Virtuality Server 2.0.We assume the the FTP / SFTP server is already used as a data source in Data Virtuality. Therefore we can use the FTP data source directly as the target schema.call "UTILS.csvExport"("sourceSchema" => '<sourceSchema>',"sourceTable" => '<sourceTable>',"targetSchema" => '<NameOfFTPdatasource>',"targetFile" => '<targetFile>',"delimiter" => '<delimiter>',"quote" => '<quote>',"header" => <trueORfalse>,"orderBy" => 'string_orderBy',"encoding" => '<encoding>');;The following parameters can be deleted if not used:targetFile delimiter quote header order by encoding

CSV Export with FTP UploadHow-To

JohnCommunity Manager

When transforming JSON documents into a tabular format, you might stumble upon certain keys that produce an error message. This document is valid JSON, but generates an error, such as "XPath syntax error at char 0 in {80x}:Separator needed after numeric literal" when parsing it in Data Virtuality. { "80x60": "https://datavirtuality.com/wp-content/uploads/sites/2/2016/09/[email protected]", } SELECT xmlTable.* FROM (call "file".getFiles('key_example.json')) f, XMLTABLE(XMLNAMESPACES( 'http://www.w3.org/2001/XMLSchema-instance' as "xsi" ),'/root' PASSING JSONTOXML('root',to_chars(f.file,'UTF-8')) COLUMNS "80x60" STRING PATH '80x60' ) "xmlTable" This is due to the fact that Data Virtuality internally transforms the JSON document into an XML document for easier parsing. Since an identifier in XML may not start with a number, it gets escaped and rewritten to _u0038_0x60. Hence, you will need to pass this value as an XPath expression for referring to your JSON k

Handling special characters in JSON keys during transformationTroubleshooting

JohnCommunity Manager

Switching the Internal Configuration Database to PostgreSQLHow-To

The following changes are only mandatory for Data Virtuality Server versions <= 2.1.5. Any later version comes with an embedded PostgreSQL configuration database by default and no changes are needed.Data Virtuality uses a configuration database to store internal information, such as data source credentials, metadata and runtime information. By default, this is powered by an embedded H2 database. For a production system, we strongly recommend switching to a more scalable and easier to operate DBMS. This article explains how to switch to PostgreSQL as a configuration database.Important: In case you were already using your Data Virtuality instance before, please note that the content of your old configuration database (such as data sources, data model and jobs) will not be transferred to your new PostgreSQL configuration database automatically. For creating a full export of your current configuration, use the Export functionality of Data Virtuality Studio before you start the procedure

JohnCommunity Manager

When creating tables in MySQL through Data Virtuality (e.g. when using MySQL as analytical storage) the charset of newly created tables will depend on the locale of your Java Virtual Machine (JVM).In some cases, this may not be UTF-8 but you still want to create tables in MySQL using UTF-8. To do so, you may override the locale by editing your MySQL data source and set JDBC parameters to characterEncoding=UTF-8&useUnicode=true.

Forcing MySQL table charset to UTF-8Troubleshooting

JohnCommunity Manager

When replicating tables from a MySQL data source you may run into the following error message: column "order_details" is of type bytea but expression is of type bigint.Looking at the data type of the order_details column, we can see that it contains binary data (blob).In order to replicate a column to your analytical storage, Data Virtuality needs to map the blob data type to a corresponding native data type in your analytical storage. This normally happens automatically without the need to manually intervene. In the case of binary data, however, Data Virtuality cannot automatically infer the right data type and you will have to manually provide this mapping by doing a type casting.In our example case, the column contains textual data, so we can convert it to a clob using the to_chars function and create a view with from that query: CREATE VIEW my_data_model.orders AS SELECT to_chars("order_details", 'UTF-8') FROM "MySQL.adventure.orders";; Now instead of replicating the ra

Replicating MySQL tables with binary columnsHow-To

JohnCommunity Manager

Can I split large strings into columns based on a delimiter?This can be done using the TEXTTABLE construct which has to be placed within a FROM clause. Show me howWorking with a fixed string SELECT a.*FROM TEXTTABLE( 'hello,5,world,2018-01-10,3.1415' COLUMNS col1 string, col2 integer, col3 string, col4 date, col5 double DELIMITER ',') as a;; Working with a column from a table SELECT a.*, b.*FROM "ds_pg.salesorderdetail" as a, TEXTTABLE( rowguid COLUMNS col1 string, col2 string, col3 string, col4 string, col5 string DELIMITER '-') as b;; Nesting TEXTTABLE to split an already split string again SELECT y.colSub1, z.colSub2FROM TEXTTABLE('valueA:val1:val2+23:valA:valB+2018-01-05' COLUMNS col1 string, col2 string, emptyColumn string

Using TEXTTABLE to Implement SPLIT / SPLIT_PARTHow-To

JohnCommunity Manager

Cannot add batch to invalidated cache group "X.Y.Z" or Batch not found in storage "nnnnnnnnn"How-To

Why am I seeing this error? Data Virtuality uses a part of the disk space in order to temporarily store specific types of data which either cannot or should not be kept in-memory. This relates mostly to data of type XML, CLOB, BLOB and OBJECT.By default, Data Virtuality reserves at most 50 GB on the partition where it runs and it will not exceed this limit. However, if there is just temporarily more demand (including what is already stored there) then you will get this error.Note that Data Virtuality tries to clean up this data but sometimes this takes longer than adding new data to it. I see this error, what can I do? Short-Term Solution A reboot of Data Virtuality always wipes the entire temporary data and you will start with 50 GB that can be filled during processing. Long-Term Solution You can permanently increase the temp space for Data Virtuality.Note that you should have enough space left on the system's disk (depending on what you have set) and that changing the setting require

JohnCommunity Manager

This article should help address error messages such as "PSQLException-ERROR: canceling statement due to conflict with recovery" or "FATAL: terminating connection due to conflict with recovery Detail: User query might have needed to see row versions that must be removed. Hint: In a moment you should be able to reconnect to the database and repeat your command. "This error does not originate in Data Virtuality, rather it has to do with the PostgreSQL replication configuration. Usually, that means that you are accessing a slave replica which is configured as a Hot Standby replica.In such configuration, the default behavior of PostgreSQL is that any new changes coming from the master server are killing all other queries which use the same data after waiting a very short amount of time, which is 30 seconds per default.Here is some further information on the subject:http://www.postgresql.org/docs/9.0/interactive/hot-standby.html#HOT-STANDBY-CONFLICTThe way to solve this is for older Postgre

PostgreSQL: "conflict with recovery" error messageHow-To

JohnCommunity Manager

The article provides the information needed to change the location of your Data Virtuality server temp folder.By default the location is .../dvserver/standalone/tmp.In order to change the location, you will have to adjust the standalone.conf.props.bat for Windows machines and the standalone.conf.props for Linux machines.Please add the following line to the file:set "JAVA_OPTS=%JAVA_OPTS% -Djboss.server.temp.dir=<newLocation>To use the new location, you will have to restart the Data Virtuality server.

Changing Directory of Temp FolderHow-To

JohnCommunity Manager

On AWS Redshift, it is recommended to ingest data via CSV files in an S3 bucket, rather than using SQL INSERT statements. Data Virtuality already has this strategy implemented that you can activate in the settings of your Redshift data source.Since Data Virtuality, as well as Redshift, will need to access the S3 bucket, there is some setup work required. There are two ways to handle the authentication:IAM User: Creating a new user with a secret access key and access key id that grant access to the S3 bucket. IAM Role: Creating a new role that is assumed by your Redshift cluster and EC2 instance running Data Virtuality.Using an IAM Role is generally preferred as you will not have to deal with keys that might eventually get compromised or need to be rotated regularly. Instead, AWS will handle this implicitly for you.Create an S3 bucketNavigate to the S3 section in your AWS account Click on the "Create bucket" button and follow the instructionsCreate an IAM policyNavigate to the IAM secti

Using S3 to Ingest Data into RedshiftHow-To

JohnCommunity Manager

Using the Upsert Stored Procedure to Reproduce the SQL MERGEHow-To

Overview: It is sometimes the case where a programmer needs to perform multiple inserts and updates to the data from one source table to a target table. It is in these scenarios, that it is beneficial to use a procedure which can support both of these functions simultaneously, increasing the efficiency of the query. SQL users will be familiar with the MERGE statement, which migrates changes from a source table to a destination table, however in Data Virtuality, the solution is to utilize the Upsert Stored Procedure or to enable an Upsert Job. Uses: There are a few different approaches to take when merging changes from the source table to the target table, however some key elements are required. An identity field or an array of key fields is used by DV to determine which rows from the source table can be compared to the rows in the target table, and which rows need to be added versus which rows need to be updated. We must then also specify the column or an array of columns to be updated

JohnCommunity Manager

When installing Data Virtuality in your own data center or cloud environment, there are a few best practices to adopt in order to ensure a smooth operation of Data Virtuality. This article gives an overview of those best practices. Backing up the Data Virtuality Configuration In order to be prepared for potential disaster recovery, or for partially rolling back to an older state, please set up a regular backup of the internal configuration database.To automate the backup, first create a target folder: $ mkdir -p /opt/datavirtuality/backups/dv$ chown -R datavirtuality:datavirtuality /opt/datavirtuality/backups Now create a new file called /opt/datavirtuality/run_dv_backup.sh with the following content (adapt the password parameter accordingly): #!/bin/bashcd /opt/datavirtuality/dvserver/bin/cli-export-1.0today=`date '+%Y_%m_%d-%H_%M_%S'`filename="/opt/datavirtuality/backups/dv/export-$today.sql"./export.sh --username admin --password admin --host localhost --file "$filename"gzip "$filen

Best Practices Operating Data Virtuality on LinuxHow-To

JohnCommunity Manager

SQL injections are a common security issue when users are given the freedom to construct their queries. One way to avoid them is by creating a custom procedure that acts as a wrapper, eliminating any risk of injection.Here is an example procedure for filtering hand_on.cust view using country filter.CREATE PROCEDURE hands_on.parametrizedQuery(IN filter_string STRING NOT NULL) RETURNS (City STRING NOT NULL, Company STRING NOT NULL) AS BEGIN DECLARE STRING sql_string = 'SELECT City, CompanyName FROM hands_on.cust WHERE hands_on.cust.country = DVARS.country'; EXECUTE IMMEDIATE sql_string AS City STRING, CompanyName STRING INTO #temptbl USING country=filter_string; SELECT * FROM #temptbl; END;;You can call it using a CALL functionalityCALL hands_on.parametrizedQuery('China');;Or, if the CALL is not available, you can query it like a table and specify the input variable name and value in a WHERE statement.SELECT * from hands_on.parametrizedQuery where filter_strin

Avoiding SQL Injection attacks using proceduresHow-To

140

JohnCommunity Manager

Executing external applications to retrieve AWS SecretsHow-To

Retrieving information from AWS Secrets Manager is currently on the roadmap, but in the meantime this technique can be used as a workaround. This approach can also be used to retrieve data using an application from various sources and return it to Data Virtuality extending its functionality.Please note, at the time of this writing, this approach is not available for SaaS. Python can be executed from within SaaS, but for security reason, loading Python packages is prohibited. And this solution requires the external package AWS SDK Boto to be loaded. This article assumes Data Virtuality Platform is installed on Linux, and the latest AWS CLI and AWS SDK are installed. The AWS SDK supports several programming languages, however, this article will use Python 3.10.This article consists of four parts:Prerequisites Configuration of AWS credentials Placement of Python script Creation of view in Data Virtuality SQL ConclusionPlease note, this article uses long term credentials for illustration p

JohnCommunity Manager

Securely managing DB credentials for DV Authentication Management ComponentHow-To

Storing database credentials can pose a security risk. In this article, we'll guide you through setting up DataVirtuality's Authentication Management Component as a service, allowing you to retrieve DB credentials from AWS Secrets Manager.1. PrerequisitesEnsure you have DataVirtuality set up with Authentication Management Component and have AWS CLI configured for the root user. If you haven't set up AWS CLI yet, refer to the official documentation.2. DB username/password in AWS SMAdd a key-value pair for username and password in AWS Secrets Manager and take note of the name for later use.3. Bash script for retrieving the dataAdjust the --secret-id parameter to match the name of your secret. Also, update the path in the last line to point to your DV Authentication Management Component folder.Save the script as /opt/datavirtuality/auth_mgmt_tool/bin/auth_management.sh. #!/bin/bash# Assuming your string is stored in a variable called 'input_string'input_string=$(aws secretsmanager get-sec

120

JohnCommunity Manager

Backup the Data Warehouse by Copying It to a Parallel InstanceHow-To

If you are not able to properly backup your data warehouse (DWH) with the means provided by your DBMS, there is still a way to perform a backup. Just follow these steps: Create a parallel instance of Data Virtuality Server (don't forget to configure the port offset!). Connect to the 'new' DWH as DWH (this is where your DWH will be stored to). Connect to the DWH you want to backup directly as a new data source on the parallel instance with schema name 'source DWH' and importer.tableTypes="TABLE". Execute the following Code on within datavirtuality Studio connected to the parallel instance (successful inserts will be logged):

100

JohnCommunity Manager

Query Parquet Files in Data Virtuality Using Amazon AthenaHow-To

Amazon Athena is a serverless querying service, offered as one of the many services available through the Amazon Web Services console. Using this service can serve a variety of purposes, but the primary use of Athena is to query data directly from Amazon S3 (Simple Storage Service), without the need for a database engine. Data on S3 is typically stored as flat files, in various formats, like CSV, JSON, XML, Parquet, and many more.Apache Parquet is a method of storing data in a column-oriented fashion, which is especially beneficial to running queries over data warehouses. Columnar tables, allows for like-data to be stored on disk, by column. This makes analytical queries, like aggregations, less expensive. Parquet originates from the Apache project and is a free, open-source, component to the Hadoop ecosystem. Parquet is typically specified on a table, during creation, however the files which are created as apart of the HDFS can be transferred or integrated, into other systems for furt

120

JohnCommunity Manager

Data Virtuality doesn't support referencing a second table in a JOIN clause, such as:UPDATE "dwh.adventureworks_salesorderheader" s SET status = i.status FROM PostgreSQL.salesorderheader h WHERE h.salesorderid = s.salesorderidTo run these kinds of queries, there are two possible workarounds:Copy both tables to the same data source and use a native call: BEGIN -- drop the temporary table in the target data source if already present DROP TABLE IF EXISTS dwh.tmp_salesorderheader; -- copy the data from the original data source (PostgreSQL) to the target data source (dwh) SELECT * INTO dwh.tmp_salesorderheader FROM PostgreSQL.salesorderheader; -- UPDATE using native CALL dwh.native('UPDATE adventureworks_salesorderheader s SET status = h.status FROM tmp_salesorderheader h WHERE h.salesorderid = s.salesorderid');END;; Iterate over each row. BEGIN -- iterate over each row from the source table LOOP ON (SELECT * FROM "PostgreSQL.salesorderheader") AS s BEGIN

UPDATE statement with two tablesHow-To