Skip to main content

CData Virtuality

CData Virtuality is and enterprise-grade data virtualization platform for real-time data access, modeling, governance, and delivery.

  • 66 Topics
  • 2 Replies
66 Topics
John
JohnCommunity Manager
published in CData Virtuality
UPDATE statement with two tablesHow-To

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

100
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Google Analytics: How to migrate from Reporting API v3 to v4How-To

The Google Analytics connector comes with two procedures for generating reports:get: Uses the Reporting API v3 getV4: Uses the Reporting API v4Both procedures and APIs are largely compatible with each other, but switching from v3 to v4 still requires a few changes.This is an example call using the get procedure and therefore API v3:SELECT PARSEDATE(a.date, 'yyyyMMdd') AS "date", CAST(a.users AS integer) AS users, CAST(a.newUsers AS integer) AS newUsers, CAST(a.percentNewSessions AS float) AS percentNewSessionsFROM ( EXEC google_analytics_src.get( profile => 68903984, startDate => '2017-04-01', endDate => '2017-04-02', metrics => 'users,newUsers,percentNewSessions', dimensions => 'date', samplingLevel => 'Higher_Precision', sort => 'users' )) aSwitching this query to API v4 involves:Changing the procedure from "get" to "getV4" Renaming the "profile" parameter to "viewId" If you use the "samplin

190
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Changes to data type mapping in release 2.3.17Troubleshooting

In order to be able to compare data types across all data sources, Data Virtuality Server maps the data source's native data types to a common set of logical data types. This is commonly referred to as data type mapping. In our release 2.3.17, we introduced changes to this mapping for numeric data types.This article describes the impact of these changes and how to deal with them when upgrading to version 2.3.17 or later. Failing ReplicationsA replication fails after the upgrade because data types in the source and target are no longer aligned. In this example, we are looking at a batch replication:exec SYSADMIN.BatchUpdate('"dwh.target_table_data_types"', '"snow.DATA_TYPE_TEST"', NULL);;Which fails with this exception:[...]Caused by: org.teiid.core.TeiidProcessingException: TEIID30128 Cannot convert insert query expression projected symbol 'snow.DATA_TYPE_TEST.colbyte' of type java.math.BigInteger to insert column 'dwh.target_table_data_types.colbyte' of type java.lang.Shortat org.teii

130
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Resolving "Too many open files" DVServer exception on Linux Troubleshooting

Sometimes DVServer starts to report the following exceptions in logs: 04:46:04,054 WARNING [io.netty.channel.DefaultChannelPipeline] (NIO0) An exceptionCaught() event was fired, and it reached at the tail of the pipeline. It usually means the last handler in the pipeline did not handle the exception.: java.io.IOException: Too many open files The server may go unresponsive or even stop due to those errors.This happens because the set limit of open files is too restrictive (1024 by default), either system-wide or, most probably, for a particular user that runs DVServer. To extend the user's limit edit the system "limits.conf" configuration file adding the following lines:<username> soft nofile 65536<username> hard nofile 65536The <username> placeholder should be replaced with actual system user name or with asterisk "*" to increase the limit for all users. The "limits.conf" configuration file resides in /etc/security directory on most modern Linux distributions, but thi

30
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Attaching MongoDB Atlas Database in DVServer 2.4Troubleshooting

An attempt to attache MongoDB Atlas Database source in DVServe 2.4 using web UI wizard in Pipes or Mongo DB wizard in DVStudio leads to timeout errors like the following:Studio version: 2.4.12-SNAPSHOTServer version: 2.4.11Full technical data:com.datavirtuality.dv.gui.admin.backend.BackEndException: org.teiid.jdbc.TeiidSQLException: Message from connection template 'mongodb': Timed out after 30000 ms while waiting to connect. Client view of cluster state is {type=UNKNOWN, servers=[{address=<host name>:27017, type=UNKNOWN, state=CONNECTING, exception={com.mongodb.MongoSocketReadException: Prematurely reached end of stream}}]But it is possible to attach it with the script below:begin/* Create connection */call SYSADMIN.createConnection(name => 'mongodb', jbossCliTemplateName => 'mongodb', connectionOrResourceAdapterProperties => 'server=<server host name>,port=27017,authSource=<auth source >,database=sample_training,login=<username>,password=<password&

80
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Replicating to non-DWH-datasourcesHow-To

With version 2.4 and above, it is easy to replicate data to DWH and to other destinations.Note: If the data source does not appear in the list, please verify your JDBC connection specifies the default schema. Add “importer.defaultSchema=<default schema>” to the JDBC connection as shown below.When replicating data, simply click on the “Target data source” and select an alternate data source as a destination. This example will generate the following SQL statement to perform the update.  EXEC "UTILS.upsert"(source_table => '"demos.sah_orderdetails"', keyColumnsArray => ('salesorderid',), updateColumns => ('salesorderid',), invertUpdateColumns => TRUE, target_table => '"dwh.new_table_upsert"', surrogateKeyType => null, surrogateKeyName => null, dbmsTableCreationOptions => null, checkMaxField => null, defaultvalueIfCheckMaxFieldIsNull => null) In earlier versions, this GUI functionality is not available in Data Virtuality Studio. However, we can still per

20
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Resolving "Too many open files" DVServer exception on Linux Troubleshooting

Sometimes DVServer starts to report the following exceptions in logs: 04:46:04,054 WARNING [io.netty.channel.DefaultChannelPipeline] (NIO0) An exceptionCaught() event was fired, and it reached at the tail of the pipeline. It usually means the last handler in the pipeline did not handle the exception.: java.io.IOException: Too many open files The server may go unresponsive or even stop due to those errors.This happens because the set limit of open files is too restrictive (1024 by default), either system-wide or, most probably, for a particular user that runs DVServer. To extend the user's limit edit the system "limits.conf" configuration file adding the following lines:<username> soft nofile 65536<username> hard nofile 65536The <username> placeholder should be replaced with actual system user name or with asterisk "*" to increase the limit for all users. The "limits.conf" configuration file resides in /etc/security directory on most modern Linux distributions, but thi

10
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Transition from AdWords to Google Ads APIHow-To

Configure a Google Ads API data sourceGet the connectorFor comfortable usage of the new Google Ads API a modular connector which is currently not shipped with the Data Virtuality Server is necessary.Please reach out to Data Virtuality Support team to get installation instructions and files matching your Data Virtuality Server version.Deploy the connector Check whether the modular connector is present on the Data Virtuality Server:SELECT "name", "deployed"FROM "SYSADMIN.ModularConnectors"WHERE "name" = 'google-ads-api'If the result is empty, the connector is not yet installed. In that case, as mentioned above, please reach out to Data Virtuality Support.If the result is not empty, you can deploy the connector:CALL "SYSADMIN.deployModularConnector"("name" => 'google-ads-api', "deploy" => true);;Configure the data sourceAfter successful deployment, the Google Ads API data source can be created. The credentials are same as used in the AdWords data source:CALL "SYSADMIN.createConnecti

30
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Bulk Excel LoaderHow-To

As of versions 2.4.26 and 3.0.5 the Excel connector only supports a connection to a single Excel file. The Excel connector will eventually support connections to multiple Excel files with a single connection. In the meantime, we suggest using the following solutions. Method 1The solution consists of automating the creation of multiple connections, one connection to each Excel file. The Excel files are in the following folder structure./mnt/hgfs/vm_host_tmp_folder/excel-sample-files/├── Book1.xlsx├── Book2.xlsx├── Book3.xlsx├── Book4.xlsx└── subfolder    ├── cities.xlsx    └── customers.xlsx The first step is to create a file connection to the folder where the files are located. The connection can also be created through code:/* Create connection */call SYSADMIN.createConnection(name => 'excel_folder', jbossCliTemplateName => 'ufile', connectionOrResourceAdapterProperties => 'ParentDirectory=/mnt/hgfs/vm_host_tmp_folder/excel-sample-files/,decompressCompressedFiles=false', encr

60
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Snowflake: working with multiple warehousesHow-To

Snowflake is a fantastic database for analytical storage and for reading and writing of data. Data Virtuality's Snowflake connector supports a single connection to a single database. If you need to connect to multiple databases, it is recommended you create multiple connections, one for each database. For example, this Snowflake instance has four databases. We currently have a single connection to the test_db database./* Create connection */call SYSADMIN.createConnection(name => 'snowflake_dv', jbossCliTemplateName => 'snowflake', connectionOrResourceAdapterProperties => 'host=************************.snowflakecomputing.com,db=test_db,user-name=qa,check-valid-connection-sql="select 0",warehouse=DEMO_WH', encryptedProperties => 'password=**password**');;/* Create data source */call SYSADMIN.createDatasource(name => 'snowflake_dv', translator => 'snowflake', modelProperties => 'importer.catalog=TEST_DB,importer.tableTypes="TABLE,VIEW",importer.useFullSchemaName=FALSE

190
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Permissions for developers in AD+DTAP scenariosHow-To

In scenarios, where Active Directory authentication is activated, there is no out-of-the-box DV connect role available. Additionally, we would like a developer to have access to all things they create, without an admin having to grant them permissions. In a development/test/(acceptance/)production CI/CD scenario, a different role/person will be in charge to implement the changes on a 'higher' instance.In our example we will first allow the developer role everything, but reduce the permissions later on.In detail, we prevent the developers from creating/adding/removing data sources and users. We also prevent them from setting permissions for themselves.The developer-role must exist (coming from AD/LDAP) and be assigned to developers.EXEC SYSADMIN.setPermissions("role_name" => 'developer-role', "resourceName" => '*', "permissions" => 'CRUDEAL', "condition" => NULL, "isConstraint" => true, "mask" => NULL, "maskOrder" => NULL) ;EXEC SYSADMIN.setPermissions("role_name" =

20
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Data Virtuality Power BI Custom ConnectorHow-To

The Data Virtuality LDW Power BI Connector is a Certified Connector and is shipped with Power BI desktop and the on premise data gateway.This Certified Connector does not yet contain the latest release which allows to connect Data Virtuality and Power Bi using a the PBIDS files provided via Data Virtuality Platform.To connect Data Virtuality and Power Bi using a the PBIDS files provided by Data Virtuality Platform Datashop, the Data Virtuality Platform Custom Connector needs to be installed. This article describes the installation of this Data Virtuality Platform Custom Connector.PrerequisitesInstall the Data Virtuality ODBC driver. The installer for the ODBC driver can be downloaded from here. Install the current version of Power BI Desktop.Installation of the Data Virtuality Platform Custom ConnectorDownload the Custom Connector. The downloadable connector .mez file below this article. Copy the connector .mez file to C:\Users\<user>\Documents\Power BI Desktop\Custom Connectors.

210
John
3 months ago
John
JohnCommunity Manager
published in CData Virtuality
Creating SSH tunnels for DB2 and other multiport databasesHow-To

Some databases require connections to multiple ports simultaneously. DB2 is a good example of this as you can see in the table below. Function Port (non-TLS) TLS Port Server Mapper 449 449 License Management 8470 9470 RPC/DPC 8475 9475 Sign-On Verification 8476 9476 Database Access 8471 9471 The above table has been reproduced from the IBM documentation for explanatory purposes. The complete documentation can be found here:Ports That Must Be Open to Make an ODBC, Data Transfer, OLE DB, ADO.Net, or JDBC Connection through a Firewallhttps://www.ibm.com/support/pages/ports-must-be-open-make-odbc-data-transfer-ole-db-adonet-or-jdbc-connection-through-firewall The challenge arose when trying to create an SSH tunnel from Data Virtuality to a DB2. I mistakenly created a single SSH tunnel for port 449.  When I tried to connect, DB2 would refuse the connection because the driver could not establish connections on the other ports. The solution turned out to be quite

60
John
3 months ago
John
JohnCommunity Manager
posted in CData Virtuality
CData Virtuality 4.6: A Leap Forward in Data Integration

Hey everyone! We are thrilled to announce the launch of CData Virtuality 4.6! Just eight weeks after acquiring Data Virtuality, we’ve reached a significant milestone that showcases our commitment to leading the data integration market. Here are the highlights: Seamless Integration of CData Connectors Under the expert leadership of Dr. Nick Golovin, our team has developed a framework that allows easy integration of any CData Driver into CData Virtuality. This advancement simplifies the addition of new connectors, enabling seamless integration of over 300 CData connectors within just a few days. 18 New Connectors Added in Record Time We’ve already integrated the first 18 CData Connectors into CData Virtuality 4.6, including: • Amazon DynamoDB• Azure Analysis Services• CData Connect Cloud• CosmosDB• ElasticSearch• Google Big Query• JDBC-ODBC Bridge• Microsoft Dataverse• Microsoft Excel• Monday.com• Neo4J• NetSuite• SAP (multiple products)• SQL Analysis Services The connector to CData Conn

0
John
4 months ago
John
JohnCommunity Manager
posted in CData Virtuality
Insights from Our Recent Webinar: "Five Styles of Data Integration"

Thank you to everyone who joined our recent webinar featuring Kevin Petrie and Nick Golovin on "Five Styles of Data Integration." We gathered some insightful questions from the audience and have provided detailed answers below. We hope these will help you better understand the different data integration techniques and how they can be applied in various scenarios. Question 1: Competitive Advantage of Reverse ETLQuestion: Can you please tell me if Reverse ETL has any competitive advantage over the other techniques? Especially regarding removing silos?Answer: Reverse ETL, where data is moved from a data warehouse back into an application, complements rather than competes with other data integration techniques. It's not typically used to remove silos but to enhance application data with insights generated from the data warehouse. Question 2: Managing Dual VirtualizationsQuestion: What if we have two virtualizations: SAP Data Sphere for ERP and another virtualization for Non-ERP data?Answer

0
John
5 months ago
John
JohnCommunity Manager
published in CData Virtuality
CData Software Acquires Data Virtuality | FAQF.A.Q.

 We’re excited to announce the acquisition of Data Virtuality, a leading enterprise data virtualization provider. Below is a brief FAQ designed to provide answers to a few common questions regarding this news.   If you have additional questions not covered in this FAQ, please contact us at [email protected] and a member of our team will assist you.   What is CData Software? CData Software is a leading provider of data access and connectivity. Our standards-based solutions streamline data access and insulate customers from the complexities of integration across ETL/ELT, data virtualization, B2B data integration and embedded connectivity. Leading applications across SaaS, data management, integration, analytics & BI, data science, AI & machine learning, and data governance depend on CData to expand the connectivity offered within the platforms they provide their customers.   Ultimately, CData strives to simplify connectivity between all of the applications and data sources that p

2341
S
6 months ago
Virtual Event: Data Virtualization, Reimagined Ma”arch 14, 2024 style=

Badge winners

Show all badges
Terms and ConditionsCookie settings