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.

2 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 .. 8 DATA TYPES .. 8 Internationalization .. 9 STATEMENTS .. 9 SELECT STATEMENT: CLAUSES ..10 INSERT / UPDATE /DELETE: CLAUSES.

3 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.

4 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 .. 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.

5 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.

6 67 VQL STATEMENTS OF DATABASES, USERS AND 69 Creating 69 Modifying and Deleting Databases .. 69 Creating 70 Modifying and Deleting Users .. 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.

7 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.

8 105 JDBC Data Sources .. 105 ODBC Data Sources .. 107 Multidimensional Data Sources .. 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.

9 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.

10 158 OPTIMIZING QUERIES .. 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.