Enterprise Data Virtualization

Ask questions, get answers, and engage in discussions about data integration solutions from CData.

66 Topics

JohnCommunity Manager

Let’s Discuss the Exciting New Features in CData Virtuality 4.8!

Hey everyone, Great news! CData has just released Virtuality 4.8, and it’s packed with fantastic new features that could significantly enhance our data virtualization experience. I wanted to share some highlights and start a conversation about how these updates might benefit us. Resource Governor One of the standout additions is the new Resource Governor feature. It gives administrators greater control by allowing them to: • Set user-specific limits on the number of concurrent queries.• Restrict the size of data retrieved from queries.• Define maximum query durations for users. These enhancements aim to optimize resource usage and improve overall system performance. What are your thoughts on these new resource management capabilities? Do you see this helping to streamline operations in your environment? Time-Zone Based Job Management Managing jobs across different time zones can be a challenge, but Virtuality 4.8 introduces a solution: • Schedule jobs according to account-level or job-

9 days ago

ColdTrackTomApprentice

asked in CData Virtuality

GoogleSheets datasource setup

Looking for config details for the google sheets connector within DataVirtuality. I’m getting the error below: This app is blockedThis app tried to access sensitive info in your Google Account. To keep your account safe, Google blocked this access.

1 month ago

RajeshApprentice

asked in CData Virtuality

Delta lake support?

Hi,I’m exploring the capabilities of CData Virtuality for our use, and am unable to find any information on support for delta lake on Azure Data Lake Store as a source. I don’t see any CData drivers for it either. Is this not a supported source? If not, is it on the roadmap? Thanks.

2 months ago

JohnCommunity Manager

posted in CData Virtuality

Announcing CData Virtuality 4.7: Role-based UI, UDF, and Trino Connector!

Hello CData Community! We’re thrilled to announce the release of CData Virtuality 4.7, bringing you innovative updates that will revolutionize your data management and governance. 🌟 Key Highlights of CData Virtuality 4.7 🌟 Granular User Roles and Permissions • Experience enhanced data governance with new role-based user permissions. • Assign precise roles to users, controlling access and functionality effortlessly. Enhanced UDF Support • Improved User Defined Functions (UDFs) support for MS SQL, with future expansions planned. • Increase your data warehouse’s capabilities directly within CData Virtuality. Exciting New Trino Connector • Integrate Trino’s powerful data processing capabilities. • Future-proof your infrastructure with planned enhancements for Hadoop MapReduce and more. Curious to explore more? Read the full blog post to dive deeper into these features and improvements. 📝 Got questions or thoughts? Drop them in the comments below! Let’s spark a conversation on

2 months ago

JohnCommunity Manager

published in CData Virtuality

How to perform quality control and vulnerability scans in Data VirtualityHow-To

IntroductionSQL code static code analysis is an essential practice that can assist in identifying coding issues, errors, and vulnerabilities in SQL code before it is executed. Static code analysis tools for SQL code can detect syntax mistakes, performance issues, and security flaws. In this guide, we will discuss the need for static code analysis of SQL code, demonstrate how to check the code using ZPA CLI, and build an example CI/CD workflow using ZPA CLI to test a folder containing SQL code and potentially submit the results to SonarQube. The need for static code analysis of SQLSQL code static code analysis is critical for finding errors and vulnerabilities in SQL code before it is executed. SQL code is vulnerable to syntax errors and performance problems, which can result in major performance degradation or security risks. A static code analysis tool can analyze SQL code for syntax errors, performance issues, and security flaws.A static code analysis tool, for example, can scan SQL

2 months ago

JohnCommunity Manager

published in CData Virtuality

Connecting PySpark via JDBC using Jupyter NotebookHow-To

Install DataVirtuality JDBC driverWindowsDownload the latest DataVirtuality JDBC driver from here.Copy the jar file to your spark install folder:D:\spark-2.3.1-bin-hadoop2.6\jarsLinuxDownload the latest DataVirtuality JDBC driver from here.Copy the jar file to your spark install folder:~/.local/lib/python3.11/site-packages/pyspark/jars/Alternative solution using SparkConfhttps://stackoverflow.com/questions/46925864/how-to-add-jdbc-drivers-to-classpath-when-using-pysparkEnabling JDBC connectionIn your DataVirtuality web Interface go to Preferences, and make sure that JDBC port is open, and write down the port number. Querying from PySpark in Jupyter NotebookNow that we have set up the JDBC driver we can connect to it and query data from it.Open a new Jupyter Notebook session and copy the followingReplace the highlights with your own credentials, and provide a working sql query.NOTE: use mm instead of mms in jdbc_url, if you are not using a SSL connection.import findsparkfindspark.init(

240

2 months ago

JohnCommunity Manager

published in CData Virtuality

Updating Data Virtuality Platform license for on-premise serversHow-To

There are two methods for updating the platform license. These methods do NOT require the server to be restarted.Web UI Copying the license file to the correct Data Virtuality folderWeb UIIn this example, my Data Virtuality server's IP address is 192.168.0.18, please replace this address with the IP address of your server. Connect to the web UI on port 8080http://192.168.0.18:8080/If you has TLS enabled please use HTTPS as your prefix.https://192.168.0.18:8080/ You should see a screen similar to Image 1. Click on the button labeled "Data Virtuality Admin". Image 1 You will then be greeted with a login screen requesting administrative credentials. In Image 2, my administrative credential name is "admin". This may be different on your system. Image 2 Once you have successfully logged in, you will see a screen similar to Image 3. On this screen you will use the "Browse" button to select your license file. And the "Upload license" button to install the license. Once installed your license

2 months ago

JohnCommunity Manager

published in CData Virtuality

Add a Column to a View That Uses Batch Job or Slowly Changing Dimensions (Simple Version)How-To

The example first shows how to manually insert a default value and then gives the command to read the values via a subquery.There are a few things to keep in mind:The snippets assume that field "Status" of type "short" shall be inserted; The sample uses the AdventureWorks database; This example supposes that the view is being replicated into the table "dwh.foo".Please be thorough and careful when implementing such operations!CREATE VIEW "views.aw" ASSELECT "salesorderheader.SalesOrderID", "salesorderheader.CustomerID", "salesorderheader.SalesPersonID"FROM "adv.salesorderheader"This creates optimization and batch job that replicates to dwh.foo.Now the column can be added. We create a dummy table (dwh.bar) with a SELECT INTO that adds the new column and automatically sets a default value for all rows. Then the original table is dropped and recreated from the dummy table again using SELECT INTO SELECT a.*, CAST(5 as short ) as "Status" INTO "dwh.bar" FROM "dwh.foo" as a;; DROP TABLE "

2 months ago

JohnCommunity Manager

published in CData Virtuality

MSSQL ENDOFMONTH ReplacementHow-To

Workaround procedure for http://technet.microsoft.com/de-de/library/ee634239.aspx CREATE VIRTUAL PROCEDURE views.endofmonth( IN getdate date ) returns (outdate date) asBEGIN DECLARE date monthlater = timestampadd( SQL_TSI_MONTH, 1, getdate ); select timestampadd( SQL_TSI_DAY, - dayofmonth(monthlater), monthlater); END;; Usage Examplescall views.endofmonth( cast ( '2012-02-07' as date ) ) result: 2012-02-29 call endofmonth( current_date ) result: Last day of current month using in WHERE clause SELECT foo from table.bar WHERE column = ( call endofmonth( current_date ) )

2 months ago

JohnCommunity Manager

published in CData Virtuality

Use Values Larger Than 2147483647 with FROM_UNIXTIMEHow-To

At present, Data Virtuality Server only supports integers in FROM_UNIXTIME and TIMESTAMPADD. As 2147483647 is the maximum positive value for a 32-bit signed binary integer, this means that larger values won't work. But we have a workaround for that.Let's consider an example where the value is greater than 2147483647:select from_unixtime( 4154192899 ) This query return the following:Error: TEIID30070 Remote org.teiid.api.exception.query.QueryResolverException: TEIID30070 The function 'from_unixtime(4154192899)' is a valid function form, but the arguments do not match a known type signature and cannot be converted using implicit type conversions.SQLState: TEIID30070ErrorCode: 0 And here's where we can use our workaround:selecttimestampadd( SQL_TSI_SECOND, cast( mod( 4154192899,60) as integer) , timestampadd ( SQL_TSI_MINUTE, cast( 4154192899/60 as integer), MODIFYTIMEZONE ({ts '1970-01-01 00:00:00'}, 'GMT' ) ))This time, it will work, and our query will return the following

2 months ago

JohnCommunity Manager

published in CData Virtuality

Improving Query Performance Using the Filter Parameter for Google AnalyticsHow-To

In some cases, query performance can be speedied up thanks to the filter parameter. In this guide, we'll consider an example where it helped to reduce load time from 30 minutes to just one minute.Here's the original request:SELECT parseDate(w."date",'yyyyMMdd') AS Datum , w."date", w.hour, 'WINDELBAR' AS "Reason Code" , w.pagePath, w.landingPagePath, w.exitPagePath, w.previousPagePath, w.nextPagePath, CAST(entrances as integer) AS entrances, CAST(exits as integer) AS exits, CAST(pageviews as integer) AS pageviewsFROM ( EXEC analytics.get( profile=>'1234567', startDate=> '2014-01-01', endDate=> '2014-01-07', metrics=>'entrances,exits,pageValue,pageviews', dimensions=>'date, hour, pagePath,landingPagePath,exitPagePath,previousPagePath,nextPagePath' )) wWHERE w."date" != '(other)' and w.pagePath Like '%login%'To optimize query performance, we can take the filter w.page

2 months ago

JohnCommunity Manager

published in CData Virtuality

Google Analytics Historical Data Using Batch Update and Avoiding SamplingHow-To

Let's suppose that we have a Google Analytics query like this, which however is subject to sampling by Google Analytics because the data are just too large. SELECT visitors, newVisits, percentNewVisits, visitorType, visitCount, daysSinceLastVisit, userDefinedValue ,parseDate("date",'yyyyMMdd') as tdateFROM table( exec GoogleAnalytics1.get( profile=>'12345678', startDate=>'2013-08-01', endDate=>curdate(), metrics=>'visitors,newVisits,percentNewVisits', dimensions=>'date,visitorType,visitCount,daysSinceLastVisit,userDefinedValue' )) aThe solution is to get the data batchwise into a data warehouse table.Step 1: execute this code. It gets the initial part of the data, for example one month. After this step, please check that the table dwh.google_history contains at least one data row. SELECT visitors, newVisits, percentNewVisits, visitorType, visitCount, daysSinceLastVisit, userDefinedValue ,parseDate("date",'yyyyMMdd') as tdateINTO dwh.google

2 months ago

JohnCommunity Manager

published in CData Virtuality

Show Running Queries on Redshift via Native InterfaceHow-To

When native queries are enabled in your Amazon Redshift data warehouse (this can be done by adding supportsNativeQueries=true as a translator property to the data source configuration), you can easily view the 200 most recent queries sent to your data warehouse:SELECT x.* FROM table( call "dwh.native"("request" => 'select query, pid, elapsed, substring from svl_qlog ORDER BY starttime DESClimit 200') ) w,ARRAYTABLE( w.tuple COLUMNS query string, pid integer , elapsed string, "substring" string ) x; For more detail, you can see Redshift documentation: View Recent Queries and SVL_QLOG.

2 months ago

JohnCommunity Manager

published in CData Virtuality

Special characters in columns are in wrong encodingTroubleshooting

Googles response from the server is in UTF-8 but some columns maintain their original charset, so your Umlauts or other special charaters will look defect ( e.g. campaignName).To fix it, you can use TO_CHARS and TO_BYTES.In Germany these columns are often encoded in windows-1252, so your view can be fixed like in this Example: create view views.adwords_clicks_per_campaignAS select adwords.customer_id.customerId ,campaignId ,CAST( TO_CHARS( TO_BYTES( "campaignName", 'windows-1252' ) , 'UTF-8' ) as string ) as "campaignName" ,accountId ,clicks from adwords.customer_id ,table ( exec "adwords"."getCAMPAIGN_PERFORMANCE_REPORT" ( adwords.customer_id.customerId ,'2013-01-01' ,curdate ( ) ,'AccountId,Clicks,campaignId,campaignName' ) ) a;;

2 months ago

JohnCommunity Manager

published in CData Virtuality

Facebook: Handle Paged ResultsHow-To

If you're using Facebook API, it's important to remember that by default, most responses to API requests are paginated by default. In this article, we describe how to work with paged results.Facebook will return a next and previous element in every request, depending on if there are pages left to fetch:next: the Graph API endpoint that will return the next page of data. If not included, this is the last page of data. Due to how pagination works with visibility and privacy it is possible that a page may be empty but contain a 'next' paging link - you should stop paging when the 'next' link no longer appears. previous: the Graph API endpoint that will return the previous page of data. If not included, this is the first page of data.To handle this in Data Virtuality, you can write a simple stored procedure using the initial endpoint, e.g. https://graph.facebook.com/v2.5/<fb_ads_number>/insights?limit=100:create VIRTUAL PROCEDURE views.my_proc_facebook_insights( IN "endpoint" string

2 months ago

JohnCommunity Manager

published in CData Virtuality

JOINs not working in SalesforceTroubleshooting

After adding a Salesforce data source, you can access your Salesforce account's data in a relational manner. However, the Salesforce data model itself has a few limitations which do not exist in regular relational databases.For example, Salesforce does not support JOINs between arbitrary tables. Tables involed in a JOIN must be in a parent-to-child or child-to-parent relationship. Otherwise, you will get the following error message:ERROR at Row:1:Column:3071Didn't understand relationship 'custom_table__cs' in FROM part of query call. If you are attempting to use a custom relationship, be sure to append the '__r' after the custom relationship name. Please reference your WSDL or the describe call for the appropriate names.There are two options to circumvent this error:Disable JOIN pushdown: By default, all JOINs are pushed down to Salesforce. You can disable this behaviour to let DataVirtuality calculate the JOIN in memory instead. This can be set by editingthe Salesforce data source and

2 months ago

JohnCommunity Manager

published in CData Virtuality

Create Dependent Replication JobsHow-To

Dependent replication jobs can help you to improve the running time and system resource consumption for queries and views that are either often required or involve data sources with a huge amount of data (several millions of rows). In this article, we'll walk you through their implementation using an example based on the Adventure Works sample database. In this scenario, there are two data sources hosted on a MySQL server and we want to improve the performance of a simple join between two tables from these sources.The guide is a step by step walkthrough and by no means complete. In the environment used for the explanation, there is a fresh setup of Data Virtuality Server and Studio with just the data warehouse connection and two data sources. If you are reading this and have an environment already in use with several data sources, materialized tables, and more, you will find some steps that you might skip (tables already materialized or replication jobs already existing and just needin

2 months ago

JohnCommunity Manager

published in CData Virtuality

Setting the Server Locale for the WEEK and DAYOFWEEK FunctionsHow-To

Depending on the locale of the server's operating system, the WEEK function used on a Sunday may return the next week, as Sunday is set as the first day of the week.This can be tested withSELECT WEEK('2014-01-05')which will return 1 on a German locale and 2 on a US locale.In order to set Data Virtuality Server to the German locale, append the following line to standalone.conf.props.bat (Windows):set "JAVA_OPTS=%JAVA_OPTS% -Duser.country=DE -Duser.language=de"On UNIX systems, append the following line to standalone.conf.props:JAVA_OPTS="$JAVA_OPTS -Duser.country=DE -Duser.language=de"

2 months ago

JohnCommunity Manager

published in CData Virtuality

Materialisation errors on Salesforce tablesTroubleshooting

When materializing Salesforce tables, or views involving a Salesforce table, you may see the following error: Next Exception: ERROR: value too long for type character varying(50)0 The reason for this is a mismatch between the table's metadata reported by Salesforce and the actual data. In some cases, Salesforce describes a column to be of type varchar(50), although this column does contain strings longer than 50 characters. To work around this issue, you may create a new view, which is based on the original Salesforce table. This view must cast the erroneous column to its correct maximum length. Then, materialize that view instead of the original Salesforce table and use only this view in all further queries. CREATE VIEW salesforce_data_model.customer__c ASSELECT CAST(Name AS varchar(500)) AS NameFROM salesforce.customer__c

2 months ago

JohnCommunity Manager

published in CData Virtuality

"invalid byte sequence" when writing from MySQL to PostgreSQLTroubleshooting

When writing data to PostgreSQL from a MySQL source, e.g. when materializing a table or view, you may encounter the following error: TEIID30504 TEIID30020 Processing exception for request -8112269768431953610.-1358838137594235897 'TEIID30504 dwhSOU:Next Exception: ERROR: invalid byte sequence for encoding "UTF8": 0x000 TEIID11013:BatchUpdateException-Batch entry 1,583 INSERT INTO dwh.mat_table_588_st0 Strings in MySQL may contain NULL characters (0x000) which are not supported by PostgreSQL. Therefore, those NULL characters need to be replaced before writing the string to PostgreSQL. This can be achieved by editing the PostgreSQL data source and adding translator parameter replaceNullCharsWith="". This will replace the NULL characters with an empty string so that the string can now be saved to PostgreSQL.

2 months ago

JohnCommunity Manager

published in CData Virtuality

Where is my mongodb column?Troubleshooting

You can't find a column in your mongodb data source, but you think its there? Please check first, if it really exists. Please open your favorite MongoDB client and type the following to find "myElement"db.collection.find( {myElement: { $exists: true } } )If this is the case, the column was added recently and is not present in the first rows, so you have to adjust your data source Parameters, and increase the mongodb.maxRowsNumber model property. Please refer to 13.9 MongoDB Connector in the Reference Guide for more information.To find out how many rows you have in your MongoDB collection typedb.collection.count()

2 months ago

JohnCommunity Manager

published in CData Virtuality

Connecting and Querying OLAPHow-To

In this article, we'll describe how to connect an OLAP cube and how to query it.PrerequisitesInstalled SSAS with a processed OLAP cube. To create an OLAP cube, you can use this tutorial, and Microsoft SQL Server Data Tools can be downloaded here; A user account with full permissions on Analysis Services database and cube; IIS Web Server providing an application which points to the SSAS instance. For detailed information on implementation, please see Microsoft documentation; Network acces from Data Virtuality to the web server.Connection Click 'Add Data Source', scroll down to Other and select OLAP XMLA. Enter the required information as shown below: Alias - name of your data source. You can enter any name, but keep in mind that the name can contain only Latin letters, digits, and underscores. DB-config: Catalog is the name of the Analysis Services Database and DataSource is the name of the Analysis Service instance (hostname ) Path - webserver path to the application (OLAP, full path:

2 months ago

JohnCommunity Manager

published in CData Virtuality

Handle multi-valued XML pathsTroubleshooting

If you have an error likeUnexpected multi-valued result was returned for XMLTable column ...you try to address a PATH in an XML document, that occurs multiple times.Example. Imagine you have this JSON document: { "data": [{ "type": "articles", "id": "1", "attributes": { "title": "JSON API paints my bikeshed!", "body": "The shortest article. Ever.", "created": "2015-05-22T14:56:29.000Z", "updated": "2015-05-22T14:56:28.000Z" }, "relationships": { "author": { "data": {"id": "42", "type": "people"} } } }], "included": [ { "attributes": "a", "attributes": "b", "attributes": "c" } ]}this will be processed to XML using jsontoxml into <?xml version='1.0' encoding='UTF-8'?><root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> <data> <type>articles</type> <id>1</id> <attributes> <title>JSON API paints my bikeshed!</title> <body>The short

2 months ago

JohnCommunity Manager

published in CData Virtuality

Create Unix TimestampsHow-To

Creating a Unix timestamp for the current timestamp: SELECT TIMESTAMPDIFF(SQL_TSI_SECOND, FROM_UNIXTIME(0), NOW());; Creating a Unix timestamp for any given date or timestamp: SELECT TIMESTAMPDIFF(SQL_TSI_SECOND, FROM_UNIXTIME(0), mydateOrTimestamp);;

2 months ago

JohnCommunity Manager

published in CData Virtuality

TEIID30020 Processing exception 'Element "foo.bar" is not defined by any relevant group.Troubleshooting

This is an error which occurs if the given object cannot be found. Typical causes and Solutions are:Syntax error -> correct the typo. The object was only temporary, like temporary tables -> Use the query in combination with a persistent table or check if you actually want to refer to the table "foo.bar" or maybe another one. The object refers to a table which Data Virtuality shows as belonging to a data source -> perform a metadata refresh of the data source to check if the object was removed or renamed at the other end Point. The object refers to an element created by yourself and you are sure that there are no typos -> Check the table SYSADMIN.ViewDefinitions, SYSADMIN.ProcDefinitions and SYS.Tables for any errors explaining why the object's definition exists but it is not available.

2 months ago

Page 1 / 3

Enterprise Data Virtualization

Badge winners

Sign up

CData Community

Scanning file for viruses.

This file cannot be downloaded