Concepts
Overview of Data Virtualization
Objectives
Benefits
Usage patterns by role
Data Virtualization with Querona
Querona vs Database server
Rationale behind Querona
Vision meets technology
Technology and Features
Broad connectivity to Data Sources
Emulation of SQL Server
Built-in Apache Spark
Data lineage
Columnar processing
No ETL
Transparent data pseudonymization
Query retargeting
Flexible deployment model
Multilanguage user interface
Virtual tables and views
Data Security
Column-Level security
Row-Level security
Data masking
Data Pseudonymization
Quickstart
Getting started
Connect
Govern
Transform
Share
Connect to the data source(s)
Prerequisites
Log in to Querona
Create a connection
Create a virtual database
Demo video
Integrate the data
Explore virtual database
Create integration view(s)
Cache the data
See also
How-to guides
How-to connect to text data using Microsoft ODBC
Prerequisites
Creating connection to the CSV files
Manual configuration of metadata
See also
Next steps
Data sources
Accounting
Blackbaud FE NXT
See also
Exact Online
See also
FreshBooks Accounting
See also
Microsoft Dynamics GP
See also
QuickBooks Desktop
See also
QuickBooks Online
See also
QuickBooks POS
See also
Reckon
See also
SAGE Intacct
See also
SAGE 50 UK
See also
Xero Accounting
See also
Collaboration
Alfresco
See also
Amazon Marketplace
See also
AWS Data Management
See also
Basecamp
See also
BOX
See also
BugZilla
See also
DigitalOcean
See also
DropBox
See also
Email
See also
EverNote
See also
FTP
See also
GMail
See also
Google Calendar
See also
Google Contacts
See also
Google Directory
See also
Google Sheets
See also
JIRA
See also
Kintone
See also
Microsoft Azure Data Management
See also
Microsoft Exchange
See also
Microsoft Office365
See also
Microsoft SharePoint
See also
SalesForce Chatter
See also
ServiceNow
See also
Slack
See also
SmartSheet
See also
Splunk
See also
SurveyMonkey
See also
Veeva Vault
See also
WordPress
See also
Zendesk
See also
CRM & Marketing
ACT! CRM
See also
Act-On
See also
Adobe Analytics
See also
Google Ad Manager (DoubleClick For Publishers)
See also
Google Ads (AdWords)
See also
Google Analytics
See also
Google Campaign Manager (DoubleClick CM)
See also
HighRise CRM
See also
HubSpot
See also
Magento
See also
MailChimp
See also
Marketo
See also
Microsoft Bing Ads
See also
Microsoft Dynamics CRM
See also
Microsoft Dynamics CRM
See also
Microsoft Dynamics365 Sales
See also
Oracle Marketing Cloud (Eloqua)
See also
Oracle Sales Cloud
See also
SalesForce & Force.com
See also
Salesforce Marketing Cloud
See also
SendGrid
See also
Sugar CRM
See also
Suite CRM
See also
Twilio
See also
Veeva CRM
See also
YouTube
See also
YouTube Analytics
See also
Zoho CRM
See also
DBMS & NoSQL & BigData
Actian Matrix
See also
Actian Vector
See also
Amazon Athena
JDBC
CData
Amazon Aurora
See also
Amazon DynamoDb
See also
Amazon Redshift
See also
Apache Cassandra
See also
Apache Drill
See also
Apache HBase
See also
Apache HIVE
See also
Apache Kafka
Apache Spark
See also
Aster Database
See also
ClickHouse
Connection settings
Unsupported data types
Cloudera
See also
Couchbase
See also
DataStax
See also
dBase
See also
Denodo
See also
ElasticSearch
See also
Exasol
See also
Firebird
See also
Google BigQuery
Google BigQuery official driver
CData BigQuery driver
Google Spanner
See also
Hortonworks Data Platform
See also
HPCC
See also
IBM Cloud SQL Query
See also
IBM Cloudant
See also
IBM DB2
See also
MariaDb
See also
MarkLogic
See also
Microsoft Access
See also
Microsoft Azure CosmosDb
See also
Microsoft Azure HDInsight
See also
Microsoft Azure Table Storage
See also
Microsoft SQL Analysis Services
See also
Microsoft SQL Server
See also
MongoDb
See also
MySql
Excluded functions
Oracle
Excluded Oracle schemas
Pervasive SQL (Btrieve)
See also
Pivotal GreenPlum
See also
PostgreSQL
Example connection strings
See Also
REDIS
See also
SAP HANA
See also
Snowflake
See also
Sybase
See also
Teradata
See also
Vertica
See also
XBase
See also
Data services & Files
CSV/TSV, PDF, MSG, Text
Key features
Built-in tables
CSV parser and reader configuration
Alternatives to the built-in File provider
Google Drive
See also
JSON
See also
LDAP Directory Services
See also
Microsoft Excel
ODBC
CData
Microsoft Excel Online
See also
Microsoft SharePoint Excel Services
See also
OData Services
See also
REST
See also
RSS
See also
SFTP
See also
XML
See also
ERP
Acumatica ERP
See also
Epicor ERP
See also
NetSuite CRM & ERP
See also
Odoo ERP
See also
SAP Business One
See also
SAP ERP
SAP NetWeaver RFC SDK x64 installation
See also
Financial & E-Commerce
Authorize.Net
See also
EBay
See also
E*Trade
See also
FedEx
See also
Microsoft Dynamics NAV
See also
Microsoft Dynamics365 Business Central
See also
Microsoft Dynamics365 Finance and Operations
See also
OFX
See also
Open Exchange Rates
See also
PayPal
See also
Quandl
See also
SAP Concur
See also
SAP Hybris C4C
See also
SAS
See also
Shopify
See also
Square
See also
Stripe
See also
UPS
See also
Generic
ADO.Net
See also
JDBC
JDBC Industry Support
JDBC prerequisites
How to configure a connection using JDBC
See also
ODBC
See also
OLE-DB
See also
Miscellaneous
Amazon S3
See also
Datarobot
See also
Google Search
See also
Microsoft Active Directory
See also
Microsoft Bing Search
See also
Microsoft Project
See also
SalesForce Einstein Analytics
See also
Wasabi Object Storage
See also
Social Network
Facebook
See also
Instagram
See also
LinkedIn
See also
Twitter
See also
Alphabetical list
User guide
Client Connectivity
Connecting from Excel
Step 1: Open Excel and select SQL Server as data source
Step 2: Enter server name taken from Querona Dashboard
Step 3: Choose a database and desired tables or views
Step 4: Save data connection
Step 5: Choose how you want to use data in your workbook
Step 6: Wait for import to finish and start exploring your data
Connecting from PowerBI
Step 1: Open PowerBI and select Microsoft SQL Server
Step 2: Enter server name taken from Querona Dashboard
Step 3: Choose a database and desired tables or views
Step 4: Choose query mode
Step 5: Optionally examine your data model
Step 6: Start building your reports
Connecting from Tableau
Step 1: Open Tableau and select Microsoft SQL Server
Step 2: Enter server name taken from Querona Dashboard
Step 3: Choose a database
Step 4: Choose desired tables or views and start exploring
Connecting from SQL Server using Linked Server
Prerequisites
Linked Server configuration
Troubleshooting
Connecting from Python
Connecting from Rtools and RStudio
Search
Search bar
Results
Filters
Results
Tags
Assign a tag
Tag details
Remove the tag
Management Tasks
Refresh statistics
Evict from Spark in-memory cache
Rebuild persistent cache
Refresh row count statistics
Connections
Create a connection
Connections
Provider
Provider parameters
Available actions
Source database
Managing connections
Listing connections
Displaying connection details
Provider details
Access rights
Dependent databases
Editing a connection
Removing a connection
CData Ado.net providers
Databases overview
Create a virtual database
Source provider for an existing connection
Source provider for the new connection
Provider parameters
Source database
Import metadata
Managing the database
Database search
Edit database
Delete database
Tables & views
Import metadata
Generate views
Database selection
Tables selection
Add view
Schemas
Access rights
Management tasks
Tags
Indexes
Managing indexes
Primary and Foreign Keys
Primary Keys
Foreign Keys
Removing Columns with Constraints
Bulk operations
Bulk delete
Management tasks
Caching
Tags
Query editor
Databases
Drag & drop
Query editor
Results
Schemas
Add schema
Edit schema
Delete schema
Tables & views
Access rights
Tags
Tables
Adding tables
Managing tables
Summary
Preview data
Columns
Primary and Foreign keys
Dependent views
Access rights
Statistics
Management tasks
Tags
Views
Adding a view
View name and type
Custom SQL
Union view
Choosing the JOIN object
Working with the editor
Join view
Choosing the JOIN object
JOIN editor
Object editor
View SQL
Columns
Managing views
Summary
Preview data
Columns
Union view
Join view
Dependency graph
Dependent views
Primary and Foreign keys
Access rights
Statistics
Caching & Partitioning
Management tasks
Tags
Caching and partitioning
Basic usage
Caching strategies
In-memory caching
Persistent caching - one table
Persistent caching - two rotated tables
Persistent caching - one table, in-memory
Persistent caching - two rotated tables, in-memory
Partitioning
In-memory partitioning modes
Persistent caching
Advanced configuration
Table storage format
Data masking
Overview of Query Retargeting
Objectives of Query Retargeting
How Query Retargeting works
Query retargeting in action
Administration Guide
Access rights
Global access rights
Object access rights
Tags
View defined Tags
Add a tag
Assign parent tag
View tag details
Nested tags
Edit tag
Engine configuration options
Monitoring overview
Statement history
Statement details
Statement parameters
Active statements
Running statement
Client connections
Client connection details
Active statements
Recent statements
Execution plan cache
Execution plan
Running jobs
See also
Broken views
Broken view details
Job and Schedule management
Schedules
Once
Daily
Weekly
Monthly
Jobs
Job management using SQL
Spark connection & management
Architecture overview
Local Spark instance
Introduction
Connecting to Spark
Local Instance Configuration
Standalone cluster
Introduction
HDInsight
Introduction
HDInsight configuration
Driver deployment
Cluster failover
Spark default database migration guide
Introduction and Prerequisites
Migration procedure
Step 1, shutdown the Spark instance
Step 2, prepare a backup of Spark metabase
Step 3, create and configure a destination folder for database files
Step 4, copy files to the destination folder
Step 5, connect to database server holding Spark metastore
Step 6, modify file locations in metabase
Step 7, start Spark and verify that data is readable
Step 8, plan to delete the source folder if not needed
SQL Dialects
SQL dialect in action
Features of SQL dialects
Connection definition customization
Query features
SQL features
Type mappings
Function mappings
Date/time function mappings
Schema analyzers
Users and roles
User management
Role management
Querona-specific views
Administrative
qua_functions
qua_materialized_views
qua_provider_tables
qua_recent_statements
Logging overview
Text files
Seq
See also
Administrative how-to articles
Metabase maintenance
How to manually edit metabase deployed on SQLite
How to migrate Spark default database using Derby as metabase
How to manually re-sync HI-LO identifiers in metabase deployed on SQLite
SQL Guide
Data types
Date and time data types
Date and Time
Numeric data types
decimal and numerics
float and real
int, bigint, smallint and tinyint
money and smallmoney
Other data types
timestamp and rowversion
uniqueidentifier
sql_variant
String data types
binary and varbinary
char and varchar
nchar and nvarchar
ntext, text, and image
Functions
Aggregate
APPROX_COUNT_DISTINCT
AVG
CHECKSUM_AGG
COUNT
COUNT_BIG
GROUPING
GROUPING_ID
KURTOSIS
MAX
MIN
SKEWNESS
STDEV
STDEVP
STRING_AGG
SUM
VAR
VARP
Analytical
CUME_DIST
FIRST_VALUE
LAG
LAST_VALUE
LEAD
PERCENT_RANK
PERCENTILE_CONT
PERCENTILE_DISC
Azure
DETECT_FACES
USER_TO_ITEM_RECOMMENDATION
Datetime
DATEADD
DATEDIFF
DATEFROMPARTS
DATENAME
DATEPART
DATETIME2FROMPARTS
DATETIMEFROMPARTS
DAY
EOMONTH
GETDATE
GETUTCDATE
ISDATE
MONTH
SMALLDATETIMEFROMPARTS
SYSDATETIME
SYSUTCDATETIME
TIMEFROMPARTS
YEAR
Expression
COALESCE
IIF
ISNULL
NULLIF
Math
ABS
ACOS
ASIN
ATAN
ATN2
CEILING
COS
COT
DEGREES
EXP
FLOOR
LOG
LOG10
PI
POWER
RADIANS
RAND
ROUND
SIGN
SIN
SQRT
SQUARE
TAN
Metadata
QUA_PARTITION_HINT
Pseudonymization
QUA_CONNECTIONSHUFFLEKEY
QUA_DESHUFFLE
QUA_SHUFFLE
Ranking
DENSE_RANK
NTILE
RANK
ROW_NUMBER
String
ASCII
CHAR
CHARINDEX
CONCAT
FORMAT
LEFT
LEN
LEVENSHTEIN
LOWER
LTRIM
NCHAR
PATINDEX
QUOTENAME
REGEX_EXTRACT
REGEX_REPLACE
REGEX_SPLIT
REPLACE
REPLICATE
REVERSE
RIGHT
RTRIM
SPACE
SPLIT
STR
STUFF
SUBSTRING
UNICODE
UPPER
System
@@DATEFIRST
@@MAX_PRECISION
@@MICROSOFTVERSION
@@OPTIONS
@@SERVICENAME
@@SPID
@@TRANCOUNT
@@VERSION
APP_NAME
ASSEMBLYPROPERTY
COL_LENGTH
COL_NAME
COLLATIONNAME
COLLATIONPROPERTY
COLLATIONPROPERTYFROMID
COLUMNPROPERTY
CURRENT_USER
DATABASE_PRINCIPAL_ID
DATABASEPROPERTYEX
DB_ID
DB_NAME
FULLTEXTCATALOGPROPERTY
FULLTEXTSERVICEPROPERTY
HAS_DBACCESS
HAS_PERMS_BY_NAME
HASHBYTES
INDEXPROPERTY
IS_MEMBER
IS_ROLEMEMBER
IS_SRVROLEMEMBER
JSON_PATH
LOAD_FILE
NEWID
OBJECT_ID
OBJECT_NAME
OBJECT_SCHEMA_NAME
OBJECTPROPERTY
OBJECTPROPERTYEX
PARSENAME
QUA_GET_INCREMENTAL_MARKER
SCHEMA_ID
SCHEMA_NAME
SERVERPROPERTY
SESSIONPROPERTY
SUSER_SNAME
SYSTEM_USER
TYPE_ID
TYPE_NAME
TYPEPROPERTY
USER_NAME
Tabular
fn_builtin_permissions
fn_helpcollations
Language Elements
Expressions
Syntax
Arguments
Expression Results
CASE Expressions
COALESCE
NULLIF
Identifiers
Unquoted Identifiers
Quoted Identifiers
Keywords
Reserved words and keywords
Non-reserved keywords
Literals
Number-type Literals
String Literals
Date/Time Literals
Operators
Arithmetic Operators
Logical Operators
Assignment Operator
Bitwise Operators
Set Operators
Comparison Operators
String Concatenation Operator
Compound Operators
Unary Operators
Queries
SELECT
SELECT statement
SELECT Clause
SELECT Examples
SELECT - GROUP BY
SELECT - HAVING
SELECT - ORDER BY
SELECT - OVER
FROM
Syntax
Arguments
Remarks
Using APPLY
Permissions
Examples
See Also
WHERE
Syntax
Arguments
Examples
See Also
Hints
Query Hints
Table Hints
Join Hints
OPTION clause
Syntax
Arguments
Examples
See Also
Predicates
IS [NOT] NULL
TOP
Syntax
Arguments
Best Practices
Compatibility Support
Examples
See Also
Statements
DDL
CREATE DATABASE
CREATE INDEX
CREATE ROLE
CREATE SCHEMA
DROP DATABASE
DROP INDEX
DROP SCHEMA
SET Statements
SET ANSI_NULLS
SET ANSI_PADDING
SET ANSI_PADDING
SET ARITHABORT
SET ARITHIGNORE
SET CONCAT_NULL_YIELDS_NULL
SET CURSOR_CLOSE_ON_COMMIT
SET DATEFIRST
SET DATEFORMAT
SET DEADLOCK_PRIORITY
SET FMTONLY
SET IMPLICIT_TRANSACTIONS
SET LANGUAGE
SET LOCK_TIMEOUT
SET NOCOUNT
SET NOEXEC
SET NUMERIC_ROUNDABORT
SET PARSEONLY
SET QUERY_GOVERNOR_COST_LIMIT
SET QUOTED_IDENTIFIER
SET ROWCOUNT
SET SHOWPLAN_ALL
SET SHOWPLAN_TEXT
SET SHOWPLAN_XML
SET STATISTICS IO
SET STATISTICS PROFILE
SET STATISTICS TIME
SET STATISTICS XML
SET TEXTSIZE
SET TRANSACTION ISOLATION LEVEL
Data Definition Language
Data Manipulation Language
Permissions statements
Session settings
Stored procedures
Administrative
qua_flush_plan_cache
qua_run_garbage_collection
qua_set_default_federator
Cache management
qua_create_view_copy
qua_evict_spark_inmem_cached_table
qua_full_load_cached_view_copy
qua_full_load_view_copy
qua_incremental_insert
qua_incremental_load_cached_view_copy
qua_invalidate_spark_cached_table
qua_populate_cached_table
qua_refresh_view
qua_set_incremental_marker
qua_set_incremental_marker_column
qua_set_reload_mode
qua_set_view_cache_mode
qua_set_view_copy_status
qua_switch_view_copy
qua_trim_view_copy
CData
qua_get_oauth_access_token_d365
qua_get_oauth_authorization_url_d365
Connection management
sp_addlinkedserver
sp_addlinkedsrvlogin
sp_dropserver
sp_testlinkedserver
sp_updatelinkedserver
Diagnostics
qua_help_jobs
qua_set_logging_level
Job management
sp_add_job
sp_add_jobstep
sp_add_schedule
sp_attach_schedule
sp_delete_job
sp_delete_jobstep
sp_delete_schedule
sp_detach_schedule
sp_help_job
sp_help_jobstep
sp_start_job
sp_stop_job
sp_update_job
sp_update_jobstep
sp_update_schedule
Utility
qua_analyze_table
qua_execute_provider_sql_on_connection
qua_update_object_statistics
qua_update_row_count_object_statistics
Metadata management
qua_get_partitioning_schema
qua_set_partitioning_schema
sp_generate_views
sp_import_metadata
Spark management
qua_spark_force_become_active
qua_spark_force_become_standby
qua_spark_instance_kill
qua_spark_instance_start
qua_spark_instance_stop
qua_spark_release
qua_spark_stop_driver
SQL Server Legacy
Honorable mentions
Transact-SQL Syntax Conventions
Multipart Names
Installation guide
Components
Querona Server
Java Runtime Environment (JRE)
Apache Spark
Data providers
Requirements and compatibility
Recommendations
Supported database management systems for caching
Server operating systems
Client operating systems
Required platforms and components
.Net Framework 4.7.2 or later
Oracle JRE x64 v8 or higher (optional)
Virtualization environments
Metadata databases
Your limits on database read operations
SQL Server editions
Active Directory
Service account
Hardware recommendations
Natural Languages
Browsers
Third-Party Software
Installation prerequisites
Installation of SQL Server Engine
Obtain the SQL Server installation package
Automated SQL Server engine installation
Step 1. Copy automation script
Step 2. Edit default passwords stored in installation script
Step 3. Run installation script
Step 4. Open Windows firewall ports
Manual SQL Server engine installation (not recommended)
Step 1. Start SQL Server setup
Step 2. Choose installation option and accept the license
Step 3. Select the components of SQL server to install on your computer
Step 4. Specify name and instance ID for the instance of SQL Server
Step 5. Database engine configuration
Step 6. Database Engine Configuration
Step 7. Run SQL Server Configuration Manager
Step 8. Open Windows firewall ports
Manual installation of Querona and Apache Spark metabases on SQL Server
Ensure Microsoft DacFramework is available on the installation machine
Deploy metabases using DacFramework
Querona server installation procedure
Step 1. Start the installation by running
Querona.msi
Step 2. Read the license text and if you agree, accept the license
Step 3. Select destination folder
Step 4. Provide database server name
Step 5. Provide configuration of the Windows service
Step 6. Configure Querona instance parameters
Step 7. In the confirmation dialog click
Install
and allow privilege elevation when asked
Post-installation steps
Installation troubleshooting
Local Apache Spark does not start
Installation was successful, but I’m unable to connect to Administrative Portal
Configuration Settings and Security
Introduction
Querona License
Apache Spark configuration tasks
Driver and Executor memory
Engine configuration
Virus scanning recommendations
How to upgrade Querona
Stop all managed local instances of Apache Spark
Proceed with Installation procedure
Installing Querona driver on Cloudera 5.10
Online installation
Offline installation
Troubleshooting
Installation
Usage
Building the package
Glossary
Querona
»
User guide
»
Views
Views
¶
This chapter describes how to create and manage views.
Adding a view
View name and type
Custom SQL
Union view
Choosing the JOIN object
Working with the editor
Join view
Choosing the JOIN object
JOIN editor
Object editor
View SQL
Columns
Managing views
Summary
Preview data
Columns
Union view
Join view
Dependency graph
Dependent views
Primary and Foreign keys
Access rights
Statistics
Caching & Partitioning
Management tasks
Tags