CData Virtuality [Archived]

In this release, we’ve implemented Federal Information Processing Standards (FIPS) support for SSL connections, ensuring that all cryptographic operations used by the CData Virtuality Server and JDBC driver can be performed using a FIPS-approved provider and FIPS-compatible keystores (you’ll need to enable it as described in our documentation).On the Server side, we’ve upgraded the embedded PostgreSQL used for the configuration database to v17 and significantly improved the structure of the Git repository (which means that it’s incompatible with the old version). Here’s what changed: we’ve improved the storage of recommended indexes, schedules and jobs, foreign functions, users and their roles and changed some other storage locations. Job owner, executor, and "enabled" flag are now saved in the same file as the job itself, materialized tables are stored together with their recommended optimizations (which are now stored in separate files), materialization jobs are located in the "jobs"

On the Server side, we’ve added a new REGCLASS function and integrated two new CData JDBC drivers: SharePoint and Email. We’ve also updated the CData JDBC Driver for Jira to v25.0.9389.0 and the Neo4J JDBC driver to version 6.6.1.The Server also now has a system option for user session timeout and email sending retries and response timeouts. Two other improvements extend model properties: importer.schemaPattern now supports fully-qualified schema names, and importer.tableNamePattern, fully-qualified table names.For Git Integration, we’ve added HTTPS support for remote operations and fixed a bug preventing user limits from being tracked in Git.We’ve also worked on our clustering implementation: clustering is now supported on Azure, including deployment on Azure Kubernetes Service, and we’ve fixed three bugs: one causing new nodes to fail to start and join the cluster due to serialization issues with jobs in state RUNNING, another where virtual view and procedure definitions were not dis

This release, which went out on August 5, is probably our most improvement-filled release, and, of course, it also includes several new features and a number of bug fixes.Speaking of improvements to the Server, most notably, we’ve implemented some security enhancements: we’ve disabled weak TLS protocols and cipher suites (but if you absolutely need to keep using the previous configuration, we’ve prepared a rollback instruction) and enforced strong password policy. User passwords must now comply with password complexity and management standards (for detailed information, see our documentation). This applies to creating new users, importing users and changing passwords. However, users with admin privileges can set passwords that do not comply with these rules, though we do not recommend using weak passwords. This change does not impact previously created user records, regardless of password complexity.Our Snowflake connector has also benefitted from the security and authentication improv

Hey CData Community!Got cloud apps that need to talk to on-prem data?CData Virtuality’s Cloud Agent is your secure, no-firewall-hacks-needed bridge between cloud deployments and private networks. Whether it’s SQL Server hiding behind a VPN or a legacy system in your basement server rack — Cloud Agent connects it all. Seamlessly. Securely.Check out the guide and start unifying your hybrid data: https://www.cdata.com/kb/articles/cdata-virtuality-cloud-agent.rst

Facebook Graph and Marketing API version 21 ended life yesterday. There is no action required for you at this time. Note that starting May 2024, Facebook implemented the version auto-upgrade functionality. This means between a version to be deprecated and the next available version, if an endpoint is not affected, the platform will upgrade the call to the next available version, rather than directly failing the request. This change is designed to ensure a smoother, more efficient API experience. However, for transparency, we advise against making API calls to the deprecated API versions.Our connector was verified to be compatible with versions 22 and 23. All calls will be auto-upgraded to version 22.We added several advanced metrics, which are not available in previous API versions, for page and post view insights, in particular, page_tab_views_login_top, page_tab_views_logout_top, page_total_actions, and more. Should you wish to receive an update, please connect our support team.Pleas

Facebook Graph and Marketing API version 21, currently supported by the connector, will be available until September 9, 2025. To accommodate this change, the connector is currently being updated. Depending on our findings, it may be necessary to update the connector to v23. Note that starting May 2024, Facebook implemented the version auto-upgrade functionality. This means between a version to be deprecated and the next available version, if an endpoint is not affected, the platform will upgrade the call to the next available version, rather than directly failing the request. This change is designed to ensure a smoother, more efficient API experience. However, for transparency, we advise against making API calls to the deprecated API versions.We will post an update when the connector is verified and/or updated to support v23 of the API.Please don't hesitate to let us know should you have questions about this update. 

The Google Ads API connector v18 is at its end of life. The sunset date for v18 is August 20. The earliest version of Virtuality Server, which natively supports v20 is 25.2, released last week. Recently Google updated the libraries to v21, which will be included in one of the next releases.If your server has not been updated, it will be in the final few days. Please contact our support team for further information.SaaS customers should all have received the update by now, no action is required from your side.

Use caseCompare strings that look similar but aren’t exactly the same — such as customer names, addresses, or product titles — in SQL queries.We are happy to announce that the development of built in functions to calculate string similarity using different algorithms is under way with the expected release by the end of summer 2025. In the meantime, here is an example on how to achieve this by creating your own virtual procedure. Why Fuzzy Matching?When integrating data from multiple sources or cleaning up inconsistent records, exact string matching (‘=’ or ‘LIKE’) often falls short. For example: "John Smith" vs. "Jon Smith" "Mary Johnson" vs. "Mary Jonson" Despite being nearly identical, these will not match using standard SQL operators.This is where Levenshtein similarity becomes powerful — it quantifies how "close" two strings are based on the minimum number of edits (insertions, deletions, substitutions) needed to convert one into the other. Solution: Levenshtein Similarity as a V

This release is packed with improvements: on the Server side, we’ve added support for Python code execution via OBJECTTABLE on Windows and implemented an Okta user storage provider for use with the CData Virtuality OAuth 2 Server. We’ve also updated the Parquet libraries, integrated the Microsoft Planner CData JDBC driver, improved the base URL extraction for CData Virtuality Auth 2 Tool loader (for better handling of path prefixes), expanded the "SYSADMIN.WebBusinessDataShopPublished" system table (now it includes resource descriptions and types), extended the "importProcedure", "importView", and "importFunction" SYSADMIN procedures to throw SQL warnings when creation of imported objects fails, made the "loginAllowed" user property session independent, and improved metadata performance for ODBC.Clustering got special attention in this release. We’ve distributed job queue for it and fixed a number of bugs: one preventing calls to "refreshDataSource", "refreshSchema", "refreshAllDataSou

The Google Ads API connector v17 is at its end of life. The sunset date for v17 is June 4. The earliest version of Virtuality Server, which natively supports v18 is 4.10. All 25.x versions also include v18 libraries. Version v18 will remain live until September later this year.Recently Google updated the libraries to v19.1, which will be included in one of the next releases.If your server has not been updated, it will in the final few days. Please contact our support team for further information.SaaS customers should all have received the update by now, no action is required from your side.

Hi everyone,I'm working with CData Virtuality and I'm looking for an easy way to track data lineage and perform impact analysis on my virtual data layers. Basically, I want to: See where my data is coming from Understand how it's transformed Know what reports or dashboards depend on specific views or tables I’d love to hear how others are doing this! Are there built-in tools or best practices within Virtuality that make this easier?Any screenshots, tips, or example use cases would be super helpful!Thanks in advance 

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

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

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

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

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

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.

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

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-

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.

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

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(

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

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 "

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 ) )