Denodo Virtual DataPort 4.6 Advanced VQL Guide

1 Denodo Virtual DataPort Advanced VQL Guide Update Aug 16th, 2011 NOTE This document is confidential and is the property of Denodo Technologies (hereinafter Denodo ). No part of the document may be copied, photographed, transmitted electronically, stored in a document management system or reproduced by any other means without prior written permission from Denodo . Copyright 2011 This document may not be reproduced in total or in part without written permission from Denodo Technologies. Virtual DataPort Advanced VQL Guide INDEX PREFACE .. I SCOPE .. I WHO SHOULD USE THIS DOCUMENT .. I SUMMARY OF CONTENTS .. I 1 2 2 GENERAL OVERVIEW OF Virtual 3 CREATING OR DEFINING DATA .. 3 Defining Base 3 Defining Data Sources and Wrappers .. 5 Defining the Views of the Global 6 EXECUTING STATEMENTS .. 7 3 LANGUAGE FOR DEFINING AND PROCESSING DATA: VQL.

2 8 DATA TYPES .. 8 Internationalization .. 9 STATEMENTS .. 9 SELECT STATEMENT: CLAUSES ..10 INSERT / UPDATE /DELETE: CLAUSES .. 11 LOGICAL OPERATORS .. 11 COMPARISON 12 FUNCTIONS FOR CONDITIONS AND DERIVED ATTRIBUTES .. 15 Arithmetic Functions .. 16 Text Processing 17 Date Processing Functions .. 20 Type Conversion Functions .. 22 XML Processing Functions .. 22 Other Functions .. 24 Aggregation Functions .. 26 SYNTAX CONVENTIONS .. 27 Syntax of Functions and Condition Values .. 28 4 CREATING A BASE RELATION (OR BASE VIEW).. 31 MODIFYING A BASE 31 QUERY CAPABILITIES: SEARCH METHODS AND 33 Query 33 Assigning Wrappers to Search 34 Example of How a Search Method is 34 5 QUERIES: SELECT STATEMENT .. 36 FROM CLAUSE .. 38 Join Operations .. 39 Flatten View (Flattening Data Structures) .. 40 SELECT CLAUSE .. 41 Derived Attributes .. 41 WHERE CLAUSE .. 41 Conditions with Compound Values .. 42 Virtual DataPort Advanced VQL Guide GROUP BY CLAUSE.

3 42 Use of Aggregation 43 HAVING CLAUSE .. 43 UNION CLAUSE .. 43 Specifying Projections in UNION 44 ORDER BY CLAUSE .. 44 OFFSET AND 44 CONTEXT CLAUSE .. 45 TRACE 47 CASE CLAUSE .. 50 6 DEFINING A DERIVED VIEW .. 51 MODIFYING A DERIVED 52 7 INSERTIONS, UPDATES AND DELETION OF VIEWS .. 53 INSERT 53 UPDATE STATEMENT .. 54 DELETE 55 USE OF THE WITH CHECK OPTION ..55 8 TRANSACTIONS IN Virtual 57 9 STORED PROCEDURES .. 58 IMPORTING A STORED PROCEDURE .. 58 USE OF STORED PROCEDURES .. 58 PRE-DEFINED PROCEDURES .. 59 10 DEFINING OTHER ELEMENTS OF THE 62 DEFINING A DATA 62 DEFINING A 63 DEFINING .JAR 64 DEFININING JMS LISTENERS .. 64 11 CREATING DATABASES, USERS AND ACCESS 67 DATABASES IN Virtual 67 USER AND ACCESS STRUCTURES IN Virtual 67 Types of 67 Types of Access Rights .. 67 VQL STATEMENTS OF DATABASES, USERS AND 69 Creating 69 Modifying and Deleting Databases .. 69 Creating 70 Modifying and Deleting Users.

4 71 Changing the Active Database .. 72 Modifying the Privileges of a User .. 72 12 DESCRIBING CATALOG ELEMENTS .. 76 EXPORTING METADATA .. 78 IMPORTING METADATA .. 79 Virtual DataPort Advanced VQL Guide 13 LISTING ELEMENTS IN THE CATALOG .. 81 14 REMOVING ELEMENTS FROM THE 83 15 PUBLICATION OF WEB CREATION OF NEW WEB SERVICES .. 85 WEB SERVICES 89 Basic and Digest .. 89 Basic LDAP .. 89 WSS .. 90 VDP .. 90 EMBEDDED WEB CONTAINER MANAGEMENT .. 90 DEPLOYMENT AND EXPORTING OF WEB SERVICES .. 90 16 PUBLICATION OF WIDGETS .. 92 CREATE NEW WIDGETS .. 92 EXPORT A WIDGET .. 93 DEPLOYMENT AND EXPORT OF AUXILIARY WEB SERVICES .. 94 17 HELP COMMAND .. 96 18 GENERATING WRAPPERS AND DATA 98 VALID CONVERSIONS BETWEEN TYPES IN WRAPPERS AND VDP TYPES .. 99 Native-type Conversions of a Wrapper to Java Types .. 100 SPECIFYING PATHS IN Virtual 103 104 CREATING DATA SOURCES .. 105 JDBC Data Sources .. 105 ODBC Data Sources .. 107 Multidimensional Data Sources.

5 109 Data Sources for Web Services .. 110 XML Data Sources .. 113 JSON Data Sources .. 115 DF Data 117 Denodo Aracne Data Sources .. 119 Google Mini Data Sources .. 120 LDAP Data Sources .. 121 BAPI Data Sources .. 121 Custom Data 122 Data Source Configuration Properties .. 123 CREATING WRAPPERS .. 128 Execution Context and Interpolation Strings .. 128 Wrapper Metadata .. 128 JDBC 129 Multidimensional Databases Wrappers .. 132 ODBC Wrappers .. 133 WWW Wrappers .. 134 Web Services Wrappers .. 139 XML Wrappers .. 141 JSON Wrappers .. 142 DF wrappers .. 143 Denodo Aracne 145 Virtual DataPort Advanced VQL Guide Google Enterprise / Google Mini Wrappers .. 149 LDAP Wrappers .. 151 BAPI Wrappers .. 152 CUSTOM 153 Wrapper Configuration Properties .. 154 QUERY WRAPPER STATEMENTS .. 156 19 Advanced CHARACTERISTICS .. 157 MANAGEMENT OF COMPOUND-TYPE 157 Processing of Compound Types: Example .. 158 OPTIMIZING QUERIES.

6 163 Optimizing Join 163 Using the Cache .. 166 Configuring Swapping Policies .. 167 Optimize DF Data Sources .. 168 PROGRAMMING EXTENSIONS .. 168 Creation of Custom Functions .. 168 Creation of Stored Procedures .. 173 Creation of Custom Wrappers .. 174 CREATING NEW INTERNATIONALIZATION CONFIGURATIONS .. 177 EXECUTION CONTEXT OF A QUERY AND INTERPOLATION 180 ADDING VARIABLES TO SELECTION CONDITIONS (GETVAR AND SETVAR).. 181 20 APPENDICES .. 186 SYNTAX OF CONDITION 186 Arithmetic Functions .. 186 Text Processing 192 Date Processing Functions .. 199 XML Processing Functions .. 209 Type Conversion Functions .. 214 Aggregation Functions .. 218 Other Functions .. 224 SYNTAX OF SEARCH EXPRESSIONS FOR THE CONTAINS 231 Exact Terms and Phrases .. 232 Term 232 Boolean Operators .. 233 Groups .. 233 Escaping Special 233 SUPPORT FOR THE CONTAINS OPERATOR OF EACH SOURCE TYPE .. 234 Aracne .. 234 Google Enterprise / Google Mini.

7 234 CASE CLAUSE EXAMPLES .. 235 DATE AND TIME PATTERN STRINGS .. 236 REFERENCES .. 238 Virtual DataPort Advanced VQL Guide FIGURES Figure 1 Search form of a Virtual bookshop on the Internet .. 4 Figure 2 Search method for a bookshop .. 5 Figure 3 Basic primitives for specifying VQL statements .. 28 Figure 4 Rules for forming 29 Figure 5 Syntax of the statement CREATE TABLE .. 31 Figure 6 Example of creating a base view .. 31 Figure 7 Syntax of the statement ALTER 33 Figure 8 Example of how a search method is created with ALTER TABLE .. 34 Figure 9 Syntax of the SELECT statement .. 38 Figure 10 Syntax of a FLATTEN view .. 40 Figure 11 Syntax for a list of conditions .. 42 Figure 12 Syntax for a projection of the result of an union .. 44 Figure 13 Syntax of the CONTEXT clause .. 46 Figure 14 Execution 49 Figure 15 CASE 50 Figure 16 Syntax of the statement CREATE 51 Figure 17 Example of how a view is defined in accordance with others .. 51 Figure 18 Syntax of the statement ALTER VIEW.

8 52 Figure 19 Syntax of the INSERT statement .. 53 Figure 20 Syntax of the UPDATE statement .. 54 Figure 21 Syntax of the DELETE statement .. 55 Figure 22 CREATE PROCEDURE syntax .. 58 Figure 23 ALTER PROCEDURE syntax .. 58 Figure 24 Syntax of the CALL statement .. 59 Figure 25 Syntax of the statement CREATE TYPE .. 62 Figure 26 Creating an enumerated data type .. 62 Figure 27 Creating a register data type .. 63 Figure 28 Creating a data type array and the register type it 63 Figure 29 Syntax of the statement CREATE 63 Figure 30 Creation of an map of type 64 Figure 31 Syntax of the CREATE JAR statement .. 64 Figure 32 Response message sent by a JMS 64 Figure 33 Response message sent to a DML query .. 65 Figure 34 Command to create a JMS listener: CREATE LISTENER JMS .. 65 Figure 35 Command to enable/disable a JMS listener: ALTER LISTENER 66 Figure 36 Syntax of the CREATE DATABASE statement ..69 Figure 37 Simplified syntax of the ALTER DATABASE 70 Figure 38 Syntax of the CREATE USER 71 Figure 39 Syntax of the ALTER USER statement.

9 71 Figure 40 Syntax of the CONNECT and CLOSE 72 Figure 41 Syntax of the GRANT/REVOKE clauses for Databases .. 73 Figure 42 Syntax of the clauses GRANT/REVOKE for Databases .. 74 Figure 43 Syntax of the clauses GRANT/REVOKE for views .. 75 Figure 44 Example of assigning privileges to users .. 75 Figure 45 Syntax of the statement 77 Figure 46 Syntax of the statement 82 Figure 47 Syntax of the statement DROP .. 83 Figure 48 Syntax of the CREATE WEBSERVICE 87 Figure 49 Syntax of the WEBCONTAINER 90 Figure 50 Syntax of the DEPLOY, EXPORT WAR and EXPORT WSDL statement .. 91 Figure 51 Syntax of the CREATE WIDGET statement .. 92 Figure 52 EXPORT WIDGET syntax .. 94 Figure 53 Syntax of the DEPLOY, UNDEPLOY and EXPORT 94 Figure 54 Syntax of the EXPORT WIDGET WEBSERVICE statement .. 95 Virtual DataPort Advanced VQL Guide Figure 55 Parameter to obtain the status of the embedded Web 95 Figure 56 Syntax of the statement HELP .. 97 Figure 57 Syntax to request help on the command ALTER TABLE.

10 97 Figure 58 Syntax of the CREATE DATASOURCE JDBC statement .. 106 Figure 59 Syntax of the ALTER DATASOURCE JDBC statement .. 107 Figure 60 Syntax of the CREATE DATASOURCE ODBC statement .. 108 Figure 61 Syntax of the ALTER DATASOURCE ODBC statement .. 109 Figure 62 Syntax of the CREATE DATASOURCE SAPBW 109 Figure 63 Syntax of the CREATE DATASOURCE SAPBW 110 Figure 64 Syntax of the CREATE DATASOURCE OLAP 110 Figure 65 Syntax of the CREATE DATASOURCE OLAP 110 Figure 66 Syntax of the CREATE DATASOURCE WS statement .. 112 Figure 67 Syntax of the ALTER DATASOURCE WS statement .. 113 Figure 68 Syntax of the CREATE DATASOURCE XML statement .. 114 Figure 69 Syntax of the ALTER DATASOURCE XML statement .. 115 Figure 70 Syntax of the creation statement of a JSON data 116 Figure 71 Syntax of the modification statement of a JSON data 116 Figure 72 Syntax of the CREATE DATASOURCE DF statement .. 118 Figure 73 Syntax of the ALTER DATASOURCE DF 119 Figure 74 Syntax of the create statement of an Aracne data 120 Figure 75 Syntax of the modification statement of an Aracne data 120 Figure 76 Syntax of the create statement of a Google Mini data source.