CData Virtuality

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

  • 21 Topics
  • 1 Reply

21 Topics

John
JohnCommunity Manager
published in CData Virtuality

Avoiding SQL Injection attacks using proceduresHow-To

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

70
John
15 days ago
John
JohnCommunity Manager
published in CData Virtuality

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

20
John
15 days ago
John
JohnCommunity Manager
published in CData Virtuality

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

60
John
15 days ago
John
JohnCommunity Manager
published in CData Virtuality

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
John
15 days ago
John
JohnCommunity Manager
published in CData Virtuality

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

20
John
15 days ago
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

70
John
15 days 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

50
John
15 days 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

30
John
15 days 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
15 days 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&

20
John
15 days 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

10
John
15 days 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
15 days 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

20
John
15 days 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

20
John
15 days 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

30
John
15 days 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
15 days 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.

70
John
15 days 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

20
John
15 days 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
27 days 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
2 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 connect@cdata.com 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

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

Badge winners

Show all badges
Terms and ConditionsCookie settings