USER MANUALS

Virtual DataPort VQL Guide¶

Table of Contents

Preface
- Scope
- Who Should Use This Document
- Summary of Contents
Introduction
General Overview of Virtual DataPort
- Creating or Defining Data
- Executing Statements
Language for Defining and Processing Data: VQL
- Element Names (Identifiers)
  - Using Back Quotes
- Object Descriptions
- Data Types
  - Data Types for Dates, Timestamps and Intervals
    - Rules to Build Queries that Deal with Datetime Types
  - Internationalization
- Statements
- Logical Operators
- Comparison Operators
  - Comparing Literals
  - Three-valued Logic
- Concatenation Operator
- Functions for Conditions and Derived Attributes
- Syntax Conventions
  - Syntax of Functions and Condition Values
- Comments
Functions
- Aggregation Functions
  - AVG
  - COUNT
  - FIRST
  - GROUP_CONCAT
  - LAST
  - LIST
  - MAX
  - MEDIAN
  - MIN
  - NEST
  - STDEV
  - STDEVP
  - SUM
  - VAR
  - VARP
- Conversion Functions
- Datetime Functions
  - ADDDAY
  - ADDHOUR
  - ADDMILLIS
  - ADDMINUTE
  - ADDMONTH
  - ADDSECOND
  - ADDWEEK
  - ADDYEAR
  - AT TIME ZONE
  - CONVERT_TIMEZONE
  - CURRENT_DATE
  - CURRENT_TIMESTAMP
  - EXTRACT
  - FIRSTDAYOFMONTH
  - FIRSTDAYOFWEEK
  - FORMATDATE
  - GETDAY
  - GETDAYOFWEEK
  - GETDAYOFYEAR
  - GETDAYSBETWEEN
  - GETHOUR
  - GETMILLISECOND
  - GETMINUTE
  - GETMONTH
  - GETMONTHSBETWEEN
  - GETQUARTER
  - GETSECOND
  - GETTIMEFROMMILLIS
  - GETTIMEINMILLIS
  - GETWEEK
  - GETWEEKSBETWEEN
  - GETYEAR
  - LASTDAYOFMONTH
  - LASTDAYOFWEEK
  - LOCALTIME
  - LOCALTIMESTAMP
  - MAX
  - MIN
  - NEXTWEEKDAY
  - NOW
  - PREVIOUSWEEKDAY
  - SUBTRACT
  - TO_DATE
  - TO_LOCALDATE
  - TO_TIME
  - TO_TIMESTAMP
  - TO_TIMESTAMPTZ
  - TRUNC
- JSON Functions
- Large language model functions
  - ENRICH_AI
- Numeric Functions
  - ABS
  - ACOS
  - ASIN
  - ATAN
  - ATAN2
  - CEIL
  - COS
  - COT
  - DEGREES
  - DIV
  - EXP
  - FLOOR
  - LN
  - LOG
  - MAX
  - MIN
  - MOD
  - MULT
  - PI
  - POWER
  - RADIANS
  - RAND
  - ROUND
  - SIGN
  - SIN
  - SQRT
  - SUBTRACT
  - SUM
  - TAN
  - TRUNC
- Other Functions
  - COALESCE
  - CONTEXTUALSUMMARY
  - DECRYPT
  - DECRYPT_FIXED
  - ENCRYPT
  - ENCRYPT_FIXED
  - GETVAR
  - GETSESSION
  - HASH
  - HASH_FUNCTION
  - MAP
  - NULLIF
  - PIVOTREGISTER
  - ROWNUM
  - UNPIVOTREGISTER
- Spatial Functions
- Text Functions
  - ASCII
  - BASE64_TO_BASE10
  - BASE64_TO_HEX
  - CHAR
  - CONCAT
  - CONCATLIST
  - DELETESPACES
  - ENDWITH
  - HEX_TO_BASE64
  - INSTR
  - LEFTPAD
  - LEN
  - LOWER
  - LTRIM
  - MAX
  - MIN
  - POSITION
  - PRINTF
  - PROPERCASE
  - REGEXP
  - REGEXP_COUNT
  - REMOVEACCENTS
  - REPEAT
  - REPLACE
  - REPLACEMAP
  - RIGHTPAD
  - RTRIM
  - SIMILARITY
  - SPLIT
  - STARTWITH
  - SUBSTRING / SUBSTR
  - TRIM
  - UPPER
- Window Functions
  - AVG
  - COUNT
  - CUME_DIST
  - DENSE_RANK
  - FIRST_VALUE
  - LAG
  - LAST_VALUE
  - LEAD
  - LISTAGG
  - MAX
  - MIN
  - NTILE
  - PERCENTILE_CONT
  - PERCENTILE_DISC
  - PERCENT_RANK
  - RANK
  - ROW_NUMBER
  - SUM
  - STDEV
- XML Functions
Generating Wrappers and Data Sources
- Creating Data Sources
- Creating Wrappers
- Valid Conversions Between Types in Wrappers and VDP Types
  - Native-type Conversions of a Wrapper to Java Types
  - Filters
Creating a Base View
- Query Capabilities: Search Methods and Wrappers
- Modifying a Base View
Queries: SELECT Statement
- FROM Clause
- WITH Clause
- SELECT Clause
  - Derived Attributes
- WHERE Clause
  - Conditions with Compound Values
- Group BY Clause
  - Use of Aggregation Functions
- HAVING Clause
- UNION Clause
- ORDER BY Clause
- OFFSET, FETCH and LIMIT
- CONTEXT Clause
- TRACE Clause
- CASE Expressions
  - CASE Clause Examples
Defining a Derived View
- Modifying a Derived View
- Defining an Interface View
Defining the Statistics of a View
Materialized Tables
- Creating Materialized Tables
- Inserting Data into Materialized Tables
Temporary Tables
- Creating Temporary Tables
- Privileges Required
Remote Tables
- CREATE REMOTE TABLE Command
Summaries
- CREATE SUMMARY VIEW Command
Inserts, Updates and Deletes Over Views
- INSERT Statement
- UPDATE Statement
- DELETE Statement
- Return Modified Rows
  - Limitations
- Use of WITH CHECK OPTION
Refreshing Views
- REFRESH Statement
ALTER SESSION Statement
Transactions in Virtual DataPort
Stored Procedures
- Importing a Stored Procedure
- Invoking Denodo Stored Procedures
- Predefined Stored Procedures
Tags
Defining Other Elements of the Catalog
- Creating Folders
- Defining a Data Type
- Defining a Map
- Defining JAR Extensions
- Defining JMS Listeners
- Defining Kafka Listeners
Creating Databases, Users, Roles and Access Privileges
- Creating and Modifying Virtual DataPort Databases
- Managing Users
- Ownership of Elements
Global Security Policies
Describing Catalog Elements
- Exporting Metadata
- Importing Metadata
Listing Elements in the Catalog
Removing Elements from the Catalog
Publication of Web Services
- Creating SOAP Web Services
- Modifying SOAP Web Services
- Creating REST Web Services
- Modifying REST Web Services
- Connection Parameters
- Web Services Authentication
- Embedded Web Container Management
- Deploying and Exporting SOAP and REST Web Services
RESTful Architecture
- Associations
- Navigational Queries
Help Command
Version Control Systems Integration Commands
- Statements to Work with GIT
- Commands for Environment Management
- Other Version Control Commands
  - VCSDISCARD
Resource Manager
- Managing the Plans of the Resource Manager
- Managing the Rules of the Resource Manager
Advanced Characteristics
- Management of Compound Values
  - Processing of Compound Types: Example
- Changing Settings of Virtual DataPort and the Web Container
- Using the Cache
- Configuring Swapping Policies
- Managing Internationalization Configurations
  - Replacing an I18n Map
  - Deleting an I18n Map
- Execution Context of a Query and Interpolation Strings
- Adding Variables to Selection Conditions (GETVAR and SETVAR)
Appendix
- Date and Time Pattern Strings

Add feedback