Skip to main content

Enterprise Semantic Layer

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

71 Topics
A
AnnaEmployee
posted in CData Virtuality
Announcing CData Virtuality 25.0: our next major release

We are thrilled to announce the first release of the new version 25 of CData Virtuality. Along with it comes the brand-new version of the CData Virtuality Documentation to help you get the most out of the update. The improvements on the Server side include replacing the WMIC Windows tool in the startup scripts and migrating the Server to WildFly 26.1.3 and Java 17. While we’re not making performance promises, moving to Java 17 and WildFly 26.1.3 brings important modernization benefits:  Java 17 is the next LTS after Java 11, ensuring ongoing security updates and stability; Improvements to Garbage Collection, JIT compiler, and memory handling may offer subtle performance gains; Built-in crypto improvements and reduced attack surface thanks to stronger encapsulation (JEP 403) increase security; Internal changes improve startup and module loading times; More recent and secure versions of libraries like RESTEasy, Undertow, Elytron, and more. Another implemented solution improves se

190
A
A
AnnaEmployee
posted in CData Virtuality
Announcing CData Virtuality 4.11: Key enhancements, fixes, and new integrations

On the Server side, we’ve implemented a new feature – the "importer.defaultCatalog" model property.We’ve also enhanced Google BigQuery to allow setting partitioning and clustering options for recommended optimizations and materialized tables created for them. Additionally, we’ve fixed a bug that caused INSERT and SELECT INTO queries with a subquery in a WHERE IN clause to hang in the running state.We’ve also worked on LDAP authentication, enabling hybrid LDAP and internal authorization, and fixed a bug causing "SYSADMIN.changeResourceOwnerAndExecutor" to not function properly.Also, we’ve implemented the ability to add a user-specific time zone configuration for job schedules. The Exporter now supports exporting data based on the user’s time zone, too.The REST API has also received some improvements: we’ve extended pagination support for source endpoints calling stored procedures with return values and made the "format" parameter specified in the URL take precedence over the ACCEPT head

140
A
Marijan
MarijanEmployee
posted in CData Virtuality
Exposing existing virtual views via OData

To effectively utilize OData with CData Virtuality, it's essential to meet specific requirements, including proper user permissions and the presence of primary keys in virtual views. This guide outlines these prerequisites and provides a sample SQL procedure to update existing views by defining a primary key column.Assigning user permissions Access to OData services is secured using HTTP Basic Authentication. Only users assigned the odata-role can access these services. You can assign roles using UI or directly by running a SQL query:CALL SYSADMIN.addUserRole(user_name => 'test', role_name => 'odata-role');; Keep in mind that the user still needs regular permissions assigned to one of his roles in order to access the views or tables whether the protocol used is OData or any of the others. Adding Primary keys to existing views Creating a virtual view with a primary key in CData Virtuality is simple and works exactly as you would expect it:CREATE VIEW views.view2 (a integer, b inte

390
Marijan
Marijan
MarijanEmployee
posted in CData Virtuality
Ensuring Data Quality in CData Virtuality: A Guide for Data Engineers

Why is Data Quality Important?In enterprise environments, data quality directly impacts operational efficiency, regulatory compliance, and decision-making. Poor-quality data can lead to inaccurate reports, financial losses, and wasted resources. Data engineers play a critical role in ensuring that data remains accurate, consistent, and reliable. How CData Virtuality Helps with Data Quality AssuranceCData Virtuality provides a virtualized data integration layer that allows organizations to query and analyze data from multiple sources in real time. Instead of replicating data, it offers centralized access, enabling engineers to apply quality checks and transformations without altering the original sources. Key advantages include:Real-time metadata querying to understand schema consistency. Automated data validation rules to prevent incorrect entries. Data governance controls to manage access and security. Integration capabilities with DataSpot and other data governance and quality monito

200
Marijan
Marijan
MarijanEmployee
posted in CData Virtuality
How to Utilise the Information Schema in Data Virtuality

Introduction One of the often overlooked features of CData Virtuality is the fact that it contains a complete and ready to use metadata repository containing all of the tables, views, columns and data types for all of the connected data sources as well as the virtual views created in the semantic model. These are all available as a set of ANSI-standard read-only views that are updated with every change in the underlying sources and always return the latest state. It provides a structured way to retrieve important information about your data sources without needing to manually inspect each schema.The columns view contains information about all table and view columns in the data sources configured in the CData Virtuality Server. The tables view contains information about all of the tables, and views contains information about virtual views created in CData Virtuality server. Why Use the INFORMATION_SCHEMA? Using the INFORMATION_SCHEMA enables users to:Retrieve metadata dynamically instea

170
Marijan
C
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.

651
Marijan
A
Anonymous
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

432
adamfinch
A
Anonymous
posted in CData Virtuality
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-

410
A
R
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.

581
A
A
Anonymous
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

360
A
A
Anonymous
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(

1000
A
A
Anonymous
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

230
A
A
Anonymous
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 "

150
A
A
Anonymous
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 ) ) 

60
A
A
Anonymous
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

80
A
A
Anonymous
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

40
A
A
Anonymous
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

100
A
A
Anonymous
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.

140
A
A
Anonymous
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;;

80
A
A
Anonymous
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

100
A
A
Anonymous
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

120
A
A
Anonymous
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

140
A
A
Anonymous
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"

30
A
A
Anonymous
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

30
A
A
Anonymous
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.

60
A
CData's Winter 2025 Release

Badge winners

  • Product Expert
    bgrassihas earned the badge Product Expert
  • Product Expert
    DuyNguyenhas earned the badge Product Expert
  • Product Expert
    quinnfhas earned the badge Product Expert
  • Product Expert
    JDBhas earned the badge Product Expert
  • Product Expert
    Annahas earned the badge Product Expert
Show all badges
Terms and ConditionsCookie settings