DRILL-8360: Add Provided Schema for XML Reader (#2710)
diff --git a/contrib/format-pdf/src/main/java/org/apache/drill/exec/store/pdf/PdfBatchReader.java b/contrib/format-pdf/src/main/java/org/apache/drill/exec/store/pdf/PdfBatchReader.java
index 94b4caf..fd6cec9 100644
--- a/contrib/format-pdf/src/main/java/org/apache/drill/exec/store/pdf/PdfBatchReader.java
+++ b/contrib/format-pdf/src/main/java/org/apache/drill/exec/store/pdf/PdfBatchReader.java
@@ -486,7 +486,9 @@
Date parsedDate = simpleDateFormat.parse(cell.getText());
timestamp = Instant.ofEpochMilli(parsedDate.getTime());
} catch (ParseException e) {
- logger.error("Error parsing timestamp: " + e.getMessage());
+ throw UserException.parseError(e)
+ .message("Cannot parse " + cell.getText() + " as a timestamp. You can specify a format string in the provided schema to correct this.")
+ .build(logger);
}
}
writer.setTimestamp(timestamp);
diff --git a/contrib/format-xml/.gitignore b/contrib/format-xml/.gitignore
new file mode 100644
index 0000000..9341ff4
--- /dev/null
+++ b/contrib/format-xml/.gitignore
@@ -0,0 +1,2 @@
+# Directory to store oauth tokens for testing Googlesheets Storage plugin
+/src/test/resources/logback-test.xml
diff --git a/contrib/format-xml/README.md b/contrib/format-xml/README.md
index 3c50ce2..ca32715 100644
--- a/contrib/format-xml/README.md
+++ b/contrib/format-xml/README.md
@@ -1,10 +1,10 @@
# XML Format Reader
-This plugin enables Drill to read XML files without defining any kind of schema.
+This plugin enables Drill to read XML files without defining any kind of schema.
## Configuration
Aside from the file extension, there is one configuration option:
-* `dataLevel`: XML data often contains a considerable amount of nesting which is not necesarily useful for data analysis. This parameter allows you to set the nesting level
+* `dataLevel`: XML data often contains a considerable amount of nesting which is not necesarily useful for data analysis. This parameter allows you to set the nesting level
where the data actually starts. The levels start at `1`.
The default configuration is shown below:
@@ -22,6 +22,21 @@
## Data Types
All fields are read as strings. Nested fields are read as maps. Future functionality could include support for lists.
+## Provided Schema
+The XML Format Reader supports provided inline schemas. An example query might be:
+
+```sql
+SELECT * FROM table(cp.`xml/simple_with_datatypes.xml`(type => 'xml',
+ schema => 'inline=(`int_field` INT, `bigint_field` BIGINT,
+ `float_field` FLOAT, `double_field` DOUBLE,
+ `boolean_field` BOOLEAN, `date_field` DATE,
+ `time_field` TIME, `timestamp_field` TIMESTAMP,
+ `string_field` VARCHAR,
+ `date2_field` DATE properties {`drill.format` = `MM/dd/yyyy`})'));
+```
+
+Current implementation only supports provided schema for scalar data types.
+
### Attributes
XML events can have attributes which can also be useful.
```xml
@@ -33,8 +48,8 @@
</book>
```
-In the example above, the `title` field contains two attributes, the `binding` and `subcategory`. In order to access these fields, Drill creates a map called `attributes` and
-adds an entry for each attribute with the field name and then the attribute name. Every XML file will have a field called `atttributes` regardless of whether the data actually
+In the example above, the `title` field contains two attributes, the `binding` and `subcategory`. In order to access these fields, Drill creates a map called `attributes` and
+adds an entry for each attribute with the field name and then the attribute name. Every XML file will have a field called `atttributes` regardless of whether the data actually
has attributes or not.
```xml
@@ -65,7 +80,7 @@
If you queried this data in Drill you'd get the table below:
```sql
-SELECT *
+SELECT *
FROM <path>.`attributes.xml`
```
@@ -82,7 +97,7 @@
## Limitations: Malformed XML
Drill can read properly formatted XML. If the XML is not properly formatted, Drill will throw errors. Some issues include illegal characters in field names, or attribute names.
-Future functionality will include some degree of data cleaning and fault tolerance.
+Future functionality will include some degree of data cleaning and fault tolerance.
## Limitations: Schema Ambiguity
XML is a challenging format to process as the structure does not give any hints about the schema. For example, a JSON file might have the following record:
@@ -126,13 +141,13 @@
</otherField>
</record>
```
-In this example, there is no way for Drill to know whether `listField` is a `list` or a `map` because it only has one entry.
+In this example, there is no way for Drill to know whether `listField` is a `list` or a `map` because it only has one entry.
## Future Functionality
* **Build schema from XSD file or link**: One of the major challenges of this reader is having to infer the schema of the data. XML files do provide a schema although this is not
- required. In the future, if there is interest, we can extend this reader to use an XSD file to build the schema which will be used to parse the actual XML file.
-
+ required. In the future, if there is interest, we can extend this reader to use an XSD file to build the schema which will be used to parse the actual XML file.
+
* **Infer Date Fields**: It may be possible to add the ability to infer data fields.
-* **List Support**: Future functionality may include the ability to infer lists from data structures.
\ No newline at end of file
+* **List Support**: Future functionality may include the ability to infer lists from data structures.
diff --git a/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLBatchReader.java b/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLBatchReader.java
index 52a2b6d..579652a 100644
--- a/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLBatchReader.java
+++ b/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLBatchReader.java
@@ -28,6 +28,7 @@
import org.apache.drill.exec.physical.impl.scan.v3.file.FileSchemaNegotiator;
import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
import org.apache.drill.exec.store.dfs.easy.EasySubScan;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -59,6 +60,12 @@
dataLevel = readerConfig.dataLevel;
file = negotiator.file();
+ // Add schema if provided
+ if (negotiator.providedSchema() != null) {
+ TupleMetadata schema = negotiator.providedSchema();
+ negotiator.tableSchema(schema, false);
+ }
+
ResultSetLoader loader = negotiator.build();
rootRowWriter = loader.writer();
diff --git a/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLReader.java b/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLReader.java
index b3af9d2..8b23ac7 100644
--- a/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLReader.java
+++ b/contrib/format-xml/src/main/java/org/apache/drill/exec/store/xml/XMLReader.java
@@ -31,6 +31,7 @@
import org.apache.drill.exec.store.ImplicitColumnUtils.ImplicitColumns;
import org.apache.drill.exec.vector.accessor.ScalarWriter;
import org.apache.drill.exec.vector.accessor.TupleWriter;
+import org.apache.drill.shaded.guava.com.google.common.base.Strings;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
@@ -43,6 +44,13 @@
import javax.xml.stream.events.XMLEvent;
import java.io.Closeable;
import java.io.InputStream;
+import java.text.ParseException;
+import java.text.SimpleDateFormat;
+import java.time.Instant;
+import java.time.LocalDate;
+import java.time.LocalTime;
+import java.time.format.DateTimeFormatter;
+import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;
@@ -177,7 +185,7 @@
currentEvent = nextEvent;
// Process the event
- processEvent(currentEvent, lastEvent);
+ processEvent(currentEvent, lastEvent, reader.peek());
} catch (XMLStreamException e) {
throw UserException
.dataReadError(e)
@@ -195,7 +203,7 @@
* the self-closing events can cause schema issues with Drill specifically, if a self-closing event
* is detected prior to a non-self-closing event, and that populated event contains a map or other nested data
* Drill will throw a schema change exception.
- *
+ * <p>
* Since Drill uses Java's streaming XML parser, unfortunately, it does not provide a means of identifying
* self-closing tags. This function does that by comparing the event with the previous event and looking for
* a condition where one event is a start and the other is an ending event. Additionally, the column number and
@@ -229,7 +237,7 @@
* @param lastEvent The previous event which was processed
*/
private void processEvent(XMLEvent currentEvent,
- XMLEvent lastEvent) {
+ XMLEvent lastEvent, XMLEvent nextEvent) {
String mapName;
switch (currentEvent.getEventType()) {
@@ -282,7 +290,6 @@
attributePrefix = XMLUtils.addField(attributePrefix, fieldName);
}
- @SuppressWarnings("unchecked")
Iterator<Attribute> attributes = startElement.getAttributes();
if (attributes != null && attributes.hasNext()) {
writeAttributes(attributePrefix, attributes);
@@ -428,8 +435,70 @@
index = writer.addColumn(colSchema);
}
ScalarWriter colWriter = writer.scalar(index);
+ ColumnMetadata columnMetadata = writer.tupleSchema().metadata(index);
+ MinorType dataType = columnMetadata.schema().getType().getMinorType();
+ String dateFormat;
+
+ // Write the values depending on their data type. This only applies to scalar fields.
if (fieldValue != null && (currentState != xmlState.ROW_ENDED && currentState != xmlState.FIELD_ENDED)) {
- colWriter.setString(fieldValue);
+ switch (dataType) {
+ case BIT:
+ colWriter.setBoolean(Boolean.parseBoolean(fieldValue));
+ break;
+ case TINYINT:
+ case SMALLINT:
+ case INT:
+ colWriter.setInt(Integer.parseInt(fieldValue));
+ break;
+ case BIGINT:
+ colWriter.setLong(Long.parseLong(fieldValue));
+ break;
+ case FLOAT4:
+ case FLOAT8:
+ colWriter.setDouble(Double.parseDouble(fieldValue));
+ break;
+ case DATE:
+ dateFormat = columnMetadata.property("drill.format");
+ LocalDate localDate;
+ if (Strings.isNullOrEmpty(dateFormat)) {
+ localDate = LocalDate.parse(fieldValue);
+ } else {
+ localDate = LocalDate.parse(fieldValue, DateTimeFormatter.ofPattern(dateFormat));
+ }
+ colWriter.setDate(localDate);
+ break;
+ case TIME:
+ dateFormat = columnMetadata.property("drill.format");
+ LocalTime localTime;
+ if (Strings.isNullOrEmpty(dateFormat)) {
+ localTime = LocalTime.parse(fieldValue);
+ } else {
+ localTime = LocalTime.parse(fieldValue, DateTimeFormatter.ofPattern(dateFormat));
+ }
+ colWriter.setTime(localTime);
+ break;
+ case TIMESTAMP:
+ dateFormat = columnMetadata.property("drill.format");
+ Instant timestamp;
+ if (Strings.isNullOrEmpty(dateFormat)) {
+ timestamp = Instant.parse(fieldValue);
+ } else {
+ try {
+ SimpleDateFormat simpleDateFormat = new SimpleDateFormat(dateFormat);
+ Date parsedDate = simpleDateFormat.parse(fieldValue);
+ timestamp = Instant.ofEpochMilli(parsedDate.getTime());
+ } catch (ParseException e) {
+ throw UserException.parseError(e)
+ .message("Cannot parse " + fieldValue + " as a timestamp. You can specify a format string in the provided schema to correct this.")
+ .addContext(errorContext)
+ .build(logger);
+ }
+ }
+ colWriter.setTimestamp(timestamp);
+ break;
+ default:
+ colWriter.setString(fieldValue);
+ }
changeState(xmlState.FIELD_ENDED);
}
}
@@ -491,7 +560,11 @@
}
private TupleWriter getAttributeWriter() {
- int attributeIndex = rootRowWriter.addColumn(SchemaBuilder.columnSchema(ATTRIBUTE_MAP_NAME, MinorType.MAP, DataMode.REQUIRED));
+ int attributeIndex = rootRowWriter.tupleSchema().index(ATTRIBUTE_MAP_NAME);
+
+ if (attributeIndex == -1) {
+ attributeIndex = rootRowWriter.addColumn(SchemaBuilder.columnSchema(ATTRIBUTE_MAP_NAME, MinorType.MAP, DataMode.REQUIRED));
+ }
return rootRowWriter.tuple(attributeIndex);
}
diff --git a/contrib/format-xml/src/test/java/org/apache/drill/exec/store/xml/TestXMLReader.java b/contrib/format-xml/src/test/java/org/apache/drill/exec/store/xml/TestXMLReader.java
index 6a9fc11..260fe9c 100644
--- a/contrib/format-xml/src/test/java/org/apache/drill/exec/store/xml/TestXMLReader.java
+++ b/contrib/format-xml/src/test/java/org/apache/drill/exec/store/xml/TestXMLReader.java
@@ -32,6 +32,9 @@
import org.junit.experimental.categories.Category;
import java.nio.file.Paths;
+import java.time.Instant;
+import java.time.LocalDate;
+import java.time.LocalTime;
import static org.apache.drill.test.QueryTestUtil.generateCompressedFile;
import static org.apache.drill.test.rowSet.RowSetUtilities.mapArray;
@@ -84,6 +87,38 @@
}
@Test
+ public void testSimpleProvidedSchema() throws Exception {
+ String sql = "SELECT * FROM table(cp.`xml/simple_with_datatypes.xml` (type => 'xml', schema " +
+ "=> 'inline=(`int_field` INT, `bigint_field` BIGINT, `float_field` FLOAT, `double_field` DOUBLE, `boolean_field` " +
+ "BOOLEAN, `date_field` DATE, `time_field` TIME, `timestamp_field` TIMESTAMP, `string_field`" +
+ " VARCHAR, `date2_field` DATE properties {`drill.format` = `MM/dd/yyyy`})'))";
+ RowSet results = client.queryBuilder().sql(sql).rowSet();
+ assertEquals(2, results.rowCount());
+
+ TupleMetadata expectedSchema = new SchemaBuilder()
+ .addNullable("int_field", MinorType.INT)
+ .addNullable("bigint_field", MinorType.BIGINT)
+ .addNullable("float_field", MinorType.FLOAT4)
+ .addNullable("double_field", MinorType.FLOAT8)
+ .addNullable("boolean_field", MinorType.BIT)
+ .addNullable("date_field", MinorType.DATE)
+ .addNullable("time_field", MinorType.TIME)
+ .addNullable("timestamp_field", MinorType.TIMESTAMP)
+ .addNullable("string_field", MinorType.VARCHAR)
+ .addNullable("date2_field", MinorType.DATE)
+ .add("attributes", MinorType.MAP)
+ .buildSchema();
+
+ RowSet expected = client.rowSetBuilder(expectedSchema)
+ .addRow(1, 1000L, 1.2999999523162842, 3.3, true, LocalDate.parse("2022-01-01"), LocalTime.parse("12:04:34"), Instant.parse("2022-01-06T12:30:30Z"), "string", LocalDate.parse("2022-03-02"), mapArray())
+ .addRow(2, 2000L, 2.299999952316284, 4.3, false, LocalDate.parse("2022-02-01"), LocalTime.parse("13:04:34"), Instant.parse("2022-03-06T12:30:30Z"), null, LocalDate.parse("2022-03-01"), mapArray())
+ .build();
+
+ new RowSetComparison(expected).verifyAndClearAll(results);
+ }
+
+
+ @Test
public void testSelfClosingTags() throws Exception {
String sql = "SELECT * FROM cp.`xml/weather.xml`";
RowSet results = client.queryBuilder().sql(sql).rowSet();
diff --git a/contrib/format-xml/src/test/resources/xml/simple_array.xml b/contrib/format-xml/src/test/resources/xml/simple_array.xml
new file mode 100644
index 0000000..c734f3a
--- /dev/null
+++ b/contrib/format-xml/src/test/resources/xml/simple_array.xml
@@ -0,0 +1,44 @@
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+-->
+<dependencies>
+ <dependency>
+ <groupId>org.apache.drill.exec</groupId>
+ <array_field>
+ <value>1</value>
+ <value>2</value>
+ <value>3</value>
+ </array_field>
+ </dependency>
+
+ <dependency>
+ <groupId>org.apache.drill.exec</groupId>
+ <scope>test</scope>
+ <array_field>
+ <value>4</value>
+ <value>5</value>
+ <value>6</value>
+ </array_field>
+ </dependency>
+
+ <dependency>
+ <groupId>org.apache.drill</groupId>
+ <scope>test</scope>
+ </dependency>
+</dependencies>
diff --git a/contrib/format-xml/src/test/resources/xml/simple_with_datatypes.xml b/contrib/format-xml/src/test/resources/xml/simple_with_datatypes.xml
new file mode 100644
index 0000000..92f6296
--- /dev/null
+++ b/contrib/format-xml/src/test/resources/xml/simple_with_datatypes.xml
@@ -0,0 +1,47 @@
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one
+ or more contributor license agreements. See the NOTICE file
+ distributed with this work for additional information
+ regarding copyright ownership. The ASF licenses this file
+ to you under the Apache License, Version 2.0 (the
+ "License"); you may not use this file except in compliance
+ with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+-->
+
+<data>
+ <row>
+ <int_field>1</int_field>
+ <bigint_field>1000</bigint_field>
+ <float_field>1.3</float_field>
+ <double_field>3.3</double_field>
+ <boolean_field>true</boolean_field>
+ <date_field>2022-01-01</date_field>
+ <date2_field>03/02/2022</date2_field>
+ <time_field>12:04:34</time_field>
+ <timestamp_field>2022-01-06T12:30:30Z</timestamp_field>
+ <string_field>string</string_field>
+ </row>
+
+ <row>
+ <int_field>2</int_field>
+ <bigint_field>2000</bigint_field>
+ <float_field>2.3</float_field>
+ <double_field>4.3</double_field>
+ <boolean_field>false</boolean_field>
+ <date_field>2022-02-01</date_field>
+ <date2_field>03/01/2022</date2_field>
+ <time_field>13:04:34</time_field>
+ <timestamp_field>2022-03-06T12:30:30Z</timestamp_field>
+ </row>
+
+</data>
diff --git a/contrib/storage-http/README.md b/contrib/storage-http/README.md
index d384bed..797ab5f 100644
--- a/contrib/storage-http/README.md
+++ b/contrib/storage-http/README.md
@@ -49,23 +49,23 @@
Many APIs require parameters to be passed directly in the URL instead of as query arguments. For example, github's API allows you to query an organization's repositories with the following
URL: https://github.com/orgs/{org}/repos
-As of Drill 1.20.0, you can simply set the URL in the connection using the curly braces. If your API includes URL parameters you must include them in the `WHERE` clause in your
+As of Drill 1.20.0, you can simply set the URL in the connection using the curly braces. If your API includes URL parameters you must include them in the `WHERE` clause in your
query, or specify a default value in the configuration.
As an example, the API above, you would have to query as shown below:
```sql
-SELECT *
+SELECT *
FROM api.github
WHERE org = 'apache'
```
This query would replace the `org`in the URL with the value from the `WHERE` clause, in this case `apache`. You can specify a default value as follows: `https://someapi.com/
-{param1}/{param2=default}`. In this case, the default would be used if and only if there isn't a parameter supplied in the query.
+{param1}/{param2=default}`. In this case, the default would be used if and only if there isn't a parameter supplied in the query.
#### Limitations on URL Parameters
-* Drill does not support boolean expressions of URL parameters in queries. For instance, for the above example, if you were to include `WHERE org='apache' OR org='linux'`,
- these parameters could not be pushed down in the current state.
+* Drill does not support boolean expressions of URL parameters in queries. For instance, for the above example, if you were to include `WHERE org='apache' OR org='linux'`,
+ these parameters could not be pushed down in the current state.
* All URL parameter clauses must be equality only.
### Passing Parameters in the Query
@@ -141,6 +141,7 @@
* `query_string`: Parameters from the query are pushed down to the query string. Static parameters are pushed to the post body.
* `post_body`: Both static and parameters from the query are pushed to the post body as key/value pairs
* `json_body`: Both static and parameters from the query are pushed to the post body as json.
+* `xml_body`: Both static and parameters from the query are pushed to the post body as XML.
#### Headers
@@ -245,13 +246,14 @@
indicate a bad request or other error.
#### Input Type
-The REST plugin accepts three different types of input: `json`, `csv` and `xml`. The default is `json`. If you are using `XML` as a data type, there is an additional
-configuration option called `xmlDataLevel` which reduces the level of unneeded nesting found in XML files. You can find more information in the documentation for Drill's XML
-format plugin.
+The REST plugin accepts three different types of input: `json`, `csv` and `xml`. The default is `json`.
#### JSON Configuration
[Read the documentation for configuring json options, including schema provisioning.](JSON_Options.md)
+#### XML Configuration
+[Read the documentation for configuring XML options, including schema provisioning.](XML_Options.md)
+
#### Authorization
`authType`: If your API requires authentication, specify the authentication
@@ -263,8 +265,8 @@
`password`: The password for basic authentication.
##### Global Credentials
-If you have an HTTP plugin with multiple endpoints that all use the same credentials, you can set the `authType` to `basic` and set global
-credentials in the storage plugin configuration.
+If you have an HTTP plugin with multiple endpoints that all use the same credentials, you can set the `authType` to `basic` and set global
+credentials in the storage plugin configuration.
Simply add the following to the storage plugin configuration:
```json
@@ -280,12 +282,12 @@
#### Limiting Results
Some APIs support a query parameter which is used to limit the number of results returned by the API. In this case you can set the `limitQueryParam` config variable to the query parameter name and Drill will automatically include this in your query. For instance, if you have an API which supports a limit query parameter called `maxRecords` and you set the abovementioned config variable then execute the following query:
-
+
```sql
SELECT <fields>
FROM api.limitedApi
-LIMIT 10
-```
+LIMIT 10
+```
Drill will send the following request to your API:
```
https://<api>?maxRecords=10
@@ -298,12 +300,12 @@
If you want to use automatic pagination in Drill, [click here to read the documentation for pagination](Pagination.md).
#### errorOn400
-When a user makes HTTP calls, the response code will be from 100-599. 400 series error codes can contain useful information and in some cases you would not want Drill to throw
-errors on 400 series errors. This option allows you to define Drill's behavior on 400 series error codes. When set to `true`, Drill will throw an exception and halt execution
+When a user makes HTTP calls, the response code will be from 100-599. 400 series error codes can contain useful information and in some cases you would not want Drill to throw
+errors on 400 series errors. This option allows you to define Drill's behavior on 400 series error codes. When set to `true`, Drill will throw an exception and halt execution
on 400 series errors, `false` will return an empty result set (with implicit fields populated).
#### verifySSLCert
-Default is `true`, but when set to false, Drill will trust all SSL certificates. Useful for debugging or on internal corporate networks using self-signed certificates or
+Default is `true`, but when set to false, Drill will trust all SSL certificates. Useful for debugging or on internal corporate networks using self-signed certificates or
private certificate authorities.
#### caseSensitiveFilters
@@ -447,7 +449,7 @@
"authType": "none",
"userName": null,
"password": null,
- "postBody": null,
+ "postBody": null,
"inputType": "json",
"errorOn400": true
}
@@ -495,7 +497,7 @@
"authType": "none",
"userName": null,
"password": null,
- "postBody": null,
+ "postBody": null,
"errorOn400": true
}
}
@@ -641,24 +643,24 @@
* `_response_code`: The response code from the HTTP request. This field is an `INT`.
* `_response_message`: The response message.
* `_response_protocol`: The response protocol.
-* `_response_url`: The actual URL sent to the API.
+* `_response_url`: The actual URL sent to the API.
## Joining Data
-There are some situations where a user might want to join data with an API result and the pushdowns prevent that from happening. The main situation where this happens is when
-an API has parameters which are part of the URL AND these parameters are dynamically populated via a join.
+There are some situations where a user might want to join data with an API result and the pushdowns prevent that from happening. The main situation where this happens is when
+an API has parameters which are part of the URL AND these parameters are dynamically populated via a join.
-In this case, there are two functions `http_get_url` and `http_get` which you can use to faciliate these joins.
+In this case, there are two functions `http_get_url` and `http_get` which you can use to faciliate these joins.
* `http_request('<storage_plugin_name>', <params>)`: This function accepts a storage plugin as input and an optional list of parameters to include in a URL.
-* `http_get(<url>, <params>)`: This function works in the same way except that it does not pull any configuration information from existing storage plugins. The input url for
- the `http_get` function must be a valid URL.
+* `http_get(<url>, <params>)`: This function works in the same way except that it does not pull any configuration information from existing storage plugins. The input url for
+ the `http_get` function must be a valid URL.
### Example Queries
-Let's say that you have a storage plugin called `github` with an endpoint called `repos` which points to the url: https://github.com/orgs/{org}/repos. It is easy enough to
+Let's say that you have a storage plugin called `github` with an endpoint called `repos` which points to the url: https://github.com/orgs/{org}/repos. It is easy enough to
write a query like this:
```sql
-SELECT *
+SELECT *
FROM github.repos
WHERE org='apache'
```
diff --git a/contrib/storage-http/XML_Options.md b/contrib/storage-http/XML_Options.md
new file mode 100644
index 0000000..e53e1e8
--- /dev/null
+++ b/contrib/storage-http/XML_Options.md
@@ -0,0 +1,39 @@
+# XML Options
+Drill has a several XML configuration options to allow you to configure how Drill interprets XML files.
+
+## DataLevel
+XML data often contains a considerable amount of nesting which is not necessarily useful for data analysis. This parameter allows you to set the nesting level
+ where the data actually starts. The levels start at `1`.
+
+## Schema Provisioning
+One of the challenges of querying APIs is inconsistent data. Drill allows you to provide a schema for individual endpoints. You can do this in one of three ways:
+
+1. By providing a schema inline [See: Specifying Schema as Table Function Parameter](https://drill.apache.org/docs/plugin-configuration-basics/#specifying-the-schema-as-table-function-parameter)
+2. By providing a schema in the configuration for the endpoint.
+
+Note: At the time of writing Drill's XML reader only supports provided schema with scalar data types.
+
+## Example Configuration:
+You can set either of these options on a per-endpoint basis as shown below:
+
+```json
+"xmlOptions": {
+ "dataLevel": 1
+}
+```
+
+Or,
+```json
+"xmlOptions": {
+ "dataLevel": 2,
+ "schema": {
+ "type": "tuple_schema",
+ "columns": [
+ {
+ "name": "custom_field",
+ "type": "VARCHAR
+ }
+ ]
+ }
+}
+```
diff --git a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpApiConfig.java b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpApiConfig.java
index 91af33e..32efdbf 100644
--- a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpApiConfig.java
+++ b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpApiConfig.java
@@ -95,6 +95,7 @@
@JsonProperty
private final String inputType;
@JsonProperty
+ @Deprecated
private final int xmlDataLevel;
@JsonProperty
private final String limitQueryParam;
@@ -111,6 +112,9 @@
@JsonProperty
private final HttpJsonOptions jsonOptions;
+ @JsonProperty
+ private final HttpXmlOptions xmlOptions;
+
@JsonInclude
@JsonProperty
private final boolean verifySSLCert;
@@ -164,6 +168,7 @@
return this.caseSensitiveFilters;
}
+ @Deprecated
public int xmlDataLevel() {
return this.xmlDataLevel;
}
@@ -179,6 +184,9 @@
public HttpJsonOptions jsonOptions() {
return this.jsonOptions;
}
+ public HttpXmlOptions xmlOptions() {
+ return this.xmlOptions;
+ }
public boolean verifySSLCert() {
return this.verifySSLCert;
@@ -202,7 +210,6 @@
}
HttpApiConfig that = (HttpApiConfig) o;
return requireTail == that.requireTail
- && xmlDataLevel == that.xmlDataLevel
&& errorOn400 == that.errorOn400
&& verifySSLCert == that.verifySSLCert
&& directCredentials == that.directCredentials
@@ -218,6 +225,7 @@
&& Objects.equals(inputType, that.inputType)
&& Objects.equals(limitQueryParam, that.limitQueryParam)
&& Objects.equals(jsonOptions, that.jsonOptions)
+ && Objects.equals(xmlOptions, that.xmlOptions)
&& Objects.equals(credentialsProvider, that.credentialsProvider)
&& Objects.equals(paginator, that.paginator);
}
@@ -225,7 +233,7 @@
@Override
public int hashCode() {
return Objects.hash(url, requireTail, method, postBody, headers, params, dataPath,
- authType, inputType, xmlDataLevel, limitQueryParam, errorOn400, jsonOptions, verifySSLCert,
+ authType, inputType, limitQueryParam, errorOn400, jsonOptions, xmlOptions, verifySSLCert,
credentialsProvider, paginator, directCredentials, postParameterLocation, caseSensitiveFilters);
}
@@ -243,10 +251,10 @@
.field("caseSensitiveFilters", caseSensitiveFilters)
.field("authType", authType)
.field("inputType", inputType)
- .field("xmlDataLevel", xmlDataLevel)
.field("limitQueryParam", limitQueryParam)
.field("errorOn400", errorOn400)
.field("jsonOptions", jsonOptions)
+ .field("xmlOptions", xmlOptions)
.field("verifySSLCert", verifySSLCert)
.field("credentialsProvider", credentialsProvider)
.field("paginator", paginator)
@@ -272,7 +280,12 @@
* All POST parameters, both static and from the query, are pushed to the POST body
* as a JSON object.
*/
- JSON_BODY
+ JSON_BODY,
+ /**
+ * All POST parameters, both static and from the query, are pushed to the POST body
+ * as an XML request.
+ */
+ XML_BODY
}
public enum HttpMethod {
@@ -292,6 +305,7 @@
? HttpMethod.GET.toString() : builder.method.trim().toUpperCase();
this.url = builder.url;
this.jsonOptions = builder.jsonOptions;
+ this.xmlOptions = builder.xmlOptions;
HttpMethod httpMethod = HttpMethod.valueOf(this.method);
// Get the request method. Only accept GET and POST requests. Anything else will default to GET.
@@ -438,6 +452,7 @@
private boolean errorOn400;
private HttpJsonOptions jsonOptions;
+ private HttpXmlOptions xmlOptions;
private CredentialsProvider credentialsProvider;
@@ -479,6 +494,11 @@
return this;
}
+ public HttpApiConfigBuilder xmlOptions(HttpXmlOptions options) {
+ this.xmlOptions = options;
+ return this;
+ }
+
public HttpApiConfigBuilder requireTail(boolean requireTail) {
this.requireTail = requireTail;
return this;
@@ -539,6 +559,12 @@
return this;
}
+ /**
+ * Do not use. Use xmlOptions instead to set XML data level.
+ * @param xmlDataLevel
+ * @return
+ */
+ @Deprecated
public HttpApiConfigBuilder xmlDataLevel(int xmlDataLevel) {
this.xmlDataLevel = xmlDataLevel;
return this;
diff --git a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXMLBatchReader.java b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXMLBatchReader.java
index d5dc5b5..5aec7ff 100644
--- a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXMLBatchReader.java
+++ b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXMLBatchReader.java
@@ -26,8 +26,10 @@
import org.apache.drill.common.exceptions.UserException;
import org.apache.drill.exec.ExecConstants;
import org.apache.drill.exec.physical.impl.scan.framework.SchemaNegotiator;
+import org.apache.drill.exec.physical.impl.scan.v3.FixedReceiver;
import org.apache.drill.exec.physical.resultSet.ResultSetLoader;
import org.apache.drill.exec.physical.resultSet.RowSetLoader;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
import org.apache.drill.exec.store.ImplicitColumnUtils.ImplicitColumns;
import org.apache.drill.exec.store.http.paginator.Paginator;
import org.apache.drill.exec.store.http.util.SimpleHttp;
@@ -53,7 +55,13 @@
super(subScan);
this.subScan = subScan;
this.maxRecords = subScan.maxRecords();
- this.dataLevel = subScan.tableSpec().connectionConfig().xmlDataLevel();
+
+ // TODO Remove the XMLDataLevel parameter. For now, check both
+ if (subScan.tableSpec().connectionConfig().xmlOptions() == null) {
+ this.dataLevel = subScan.tableSpec().connectionConfig().xmlDataLevel();
+ } else {
+ this.dataLevel = subScan.tableSpec().connectionConfig().xmlOptions().getDataLevel();
+ }
}
@@ -61,7 +69,12 @@
super(subScan, paginator);
this.subScan = subScan;
this.maxRecords = subScan.maxRecords();
- this.dataLevel = subScan.tableSpec().connectionConfig().xmlDataLevel();
+
+ if (subScan.tableSpec().connectionConfig().xmlOptions() == null) {
+ this.dataLevel = subScan.tableSpec().connectionConfig().xmlDataLevel();
+ } else {
+ this.dataLevel = subScan.tableSpec().connectionConfig().xmlOptions().getDataLevel();
+ }
}
@Override
@@ -96,6 +109,12 @@
inStream = http.getInputStream();
// Initialize the XMLReader the reader
try {
+ // Add schema if provided
+ TupleMetadata finalSchema = getSchema(negotiator);
+ if (finalSchema != null) {
+ negotiator.tableSchema(finalSchema, false);
+ }
+
xmlReader = new XMLReader(inStream, dataLevel);
resultLoader = negotiator.build();
@@ -121,6 +140,36 @@
return true;
}
+ /**
+ * This function obtains the correct schema for the {@link XMLReader}. There are four possibilities:
+ * 1. The schema is provided in the configuration only. In this case, that schema will be returned.
+ * 2. The schema is provided in both the configuration and inline. These two schemas will be merged together.
+ * 3. The schema is provided inline in a query. In this case, that schema will be returned.
+ * 4. No schema is provided. Function returns null.
+ * @param negotiator {@link SchemaNegotiator} The schema negotiator with all the connection information
+ * @return The built {@link TupleMetadata} of the provided schema, null if none provided.
+ */
+ private TupleMetadata getSchema(SchemaNegotiator negotiator) {
+ if (subScan.tableSpec().connectionConfig().xmlOptions() != null &&
+ subScan.tableSpec().connectionConfig().xmlOptions().schema() != null) {
+ TupleMetadata configuredSchema = subScan.tableSpec().connectionConfig().xmlOptions().schema();
+
+ // If it has a provided schema both inline and in the config, merge the two, otherwise, return the config schema
+ if (negotiator.hasProvidedSchema()) {
+ TupleMetadata inlineSchema = negotiator.providedSchema();
+ return FixedReceiver.Builder.mergeSchemas(configuredSchema, inlineSchema);
+ } else {
+ return configuredSchema;
+ }
+ } else {
+ if (negotiator.hasProvidedSchema()) {
+ return negotiator.providedSchema();
+ }
+ }
+ return null;
+ }
+
+
@Override
public boolean next() {
boolean result;
diff --git a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXmlOptions.java b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXmlOptions.java
new file mode 100644
index 0000000..d73e576
--- /dev/null
+++ b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/HttpXmlOptions.java
@@ -0,0 +1,120 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one
+ * or more contributor license agreements. See the NOTICE file
+ * distributed with this work for additional information
+ * regarding copyright ownership. The ASF licenses this file
+ * to you under the Apache License, Version 2.0 (the
+ * "License"); you may not use this file except in compliance
+ * with the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.drill.exec.store.http;
+
+
+import com.fasterxml.jackson.annotation.JsonCreator;
+import com.fasterxml.jackson.annotation.JsonInclude;
+import com.fasterxml.jackson.annotation.JsonProperty;
+import com.fasterxml.jackson.databind.annotation.JsonDeserialize;
+import com.fasterxml.jackson.databind.annotation.JsonPOJOBuilder;
+import org.apache.drill.common.PlanStringBuilder;
+import org.apache.drill.exec.record.metadata.TupleMetadata;
+
+import java.util.Objects;
+
+@JsonInclude(JsonInclude.Include.NON_DEFAULT)
+@JsonDeserialize(builder = HttpXmlOptions.HttpXmlOptionsBuilder.class)
+public class HttpXmlOptions {
+
+ @JsonProperty
+ private final int dataLevel;
+
+ @JsonProperty
+ private final TupleMetadata schema;
+
+ @JsonCreator
+ public HttpXmlOptions(@JsonProperty("dataLevel") Integer dataLevel,
+ @JsonProperty("schema") TupleMetadata schema) {
+ this.schema = schema;
+ if (dataLevel == null || dataLevel < 1) {
+ this.dataLevel = 1;
+ } else {
+ this.dataLevel = dataLevel;
+ }
+ }
+
+ public HttpXmlOptions(HttpXmlOptionsBuilder builder) {
+ this.dataLevel = builder.dataLevel;
+ this.schema = builder.schema;
+ }
+
+
+ public static HttpXmlOptionsBuilder builder() {
+ return new HttpXmlOptionsBuilder();
+ }
+
+ @JsonProperty("dataLevel")
+ public int getDataLevel() {
+ return this.dataLevel;
+ }
+
+ @JsonProperty("schema")
+ public TupleMetadata schema() {
+ return this.schema;
+ }
+
+
+ @Override
+ public boolean equals(Object o) {
+ if (this == o) {
+ return true;
+ }
+ if (o == null || getClass() != o.getClass()) {
+ return false;
+ }
+ HttpXmlOptions that = (HttpXmlOptions) o;
+ return Objects.equals(dataLevel, that.dataLevel)
+ && Objects.equals(schema, that.schema);
+ }
+
+ @Override
+ public int hashCode() {
+ return Objects.hash(dataLevel, schema);
+ }
+
+ @Override
+ public String toString() {
+ return new PlanStringBuilder(this)
+ .field("dataLevel", dataLevel)
+ .field("schema", schema)
+ .toString();
+ }
+
+ @JsonPOJOBuilder(withPrefix = "")
+ public static class HttpXmlOptionsBuilder {
+
+ private int dataLevel;
+ private TupleMetadata schema;
+
+ public HttpXmlOptions.HttpXmlOptionsBuilder dataLevel(int dataLevel) {
+ this.dataLevel = dataLevel;
+ return this;
+ }
+
+ public HttpXmlOptions.HttpXmlOptionsBuilder schema(TupleMetadata schema) {
+ this.schema = schema;
+ return this;
+ }
+
+ public HttpXmlOptions build() {
+ return new HttpXmlOptions(this);
+ }
+ }
+}
diff --git a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/util/SimpleHttp.java b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/util/SimpleHttp.java
index d0f12f2..3568fe9 100644
--- a/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/util/SimpleHttp.java
+++ b/contrib/storage-http/src/main/java/org/apache/drill/exec/store/http/util/SimpleHttp.java
@@ -104,6 +104,7 @@
private static final int DEFAULT_TIMEOUT = 1;
private static final Pattern URL_PARAM_REGEX = Pattern.compile("\\{(\\w+)(?:=(\\w*))?}");
public static final MediaType JSON_MEDIA_TYPE = MediaType.get("application/json; charset=utf-8");
+ public static final MediaType XML_MEDIA_TYPE = MediaType.get("application/xml");
private static final OkHttpClient SIMPLE_CLIENT = new OkHttpClient.Builder()
.connectTimeout(DEFAULT_TIMEOUT, TimeUnit.SECONDS)
.writeTimeout(DEFAULT_TIMEOUT, TimeUnit.SECONDS)
@@ -365,6 +366,20 @@
RequestBody requestBody = RequestBody.create(json.toJSONString(), JSON_MEDIA_TYPE);
requestBuilder.post(requestBody);
+ } else if (apiConfig.getPostLocation() == PostLocation.XML_BODY) {
+ StringBuilder xmlRequest = new StringBuilder();
+ xmlRequest.append("<request>");
+ if (filters != null) {
+ for (Map.Entry<String, String> filter : filters.entrySet()) {
+ xmlRequest.append("<").append(filter.getKey()).append(">");
+ xmlRequest.append(filter.getValue());
+ xmlRequest.append("</").append(filter.getKey()).append(">");
+ }
+ }
+ xmlRequest.append("</request>");
+ RequestBody requestBody = RequestBody.create(xmlRequest.toString(), XML_MEDIA_TYPE);
+ requestBuilder.post(requestBody);
+
} else {
formBodyBuilder = buildPostBody(apiConfig.postBody());
requestBuilder.post(formBodyBuilder.build());
diff --git a/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestHttpPlugin.java b/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestHttpPlugin.java
index c09e3a5..32e31fc 100644
--- a/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestHttpPlugin.java
+++ b/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestHttpPlugin.java
@@ -131,11 +131,26 @@
.requireTail(false)
.build();
+ HttpXmlOptions nycXmlOptions = HttpXmlOptions.builder()
+ .dataLevel(5)
+ .build();
+
+ HttpApiConfig nycConfig = HttpApiConfig.builder()
+ .url("https://www.checkbooknyc.com/api")
+ .method("post")
+ .inputType("xml")
+ .requireTail(false)
+ .params(Arrays.asList("type_of_data", "records_from", "max_records"))
+ .postParameterLocation("xml_body")
+ .xmlOptions(nycXmlOptions)
+ .build();
+
Map<String, HttpApiConfig> configs = new HashMap<>();
configs.put("stock", stockConfig);
configs.put("sunrise", sunriseConfig);
configs.put("sunrise2", sunriseWithParamsConfig);
configs.put("pokemon", pokemonConfig);
+ configs.put("nyc", nycConfig);
HttpStoragePluginConfig mockStorageConfigWithWorkspace =
new HttpStoragePluginConfig(false, configs, 10, 1000, null, null, "", 80, "", "", "", null, PlainCredentialsProvider.EMPTY_CREDENTIALS_PROVIDER,
@@ -286,6 +301,26 @@
.dataPath("results")
.build();
+ HttpXmlOptions xmlOptions = new HttpXmlOptions.HttpXmlOptionsBuilder()
+ .dataLevel(2)
+ .build();
+
+ TupleMetadata testSchema = new SchemaBuilder()
+ .add("attributes", MinorType.MAP)
+ .addNullable("COMMON", MinorType.VARCHAR)
+ .addNullable("BOTANICAL", MinorType.VARCHAR)
+ .addNullable("ZONE", MinorType.INT)
+ .addNullable("LIGHT", MinorType.VARCHAR)
+ .addNullable("PRICE", MinorType.VARCHAR)
+ .addNullable("AVAILABILITY", MinorType.VARCHAR)
+ .buildSchema();
+
+ HttpXmlOptions xmlOptionsWithSchhema = new HttpXmlOptions.HttpXmlOptionsBuilder()
+ .dataLevel(2)
+ .schema(testSchema)
+ .build();
+
+
HttpApiConfig mockXmlConfig = HttpApiConfig.builder()
.url(makeUrl("http://localhost:%d/xml"))
.method("GET")
@@ -295,9 +330,22 @@
.password("pass")
.dataPath("results")
.inputType("xml")
- .xmlDataLevel(2)
+ .xmlOptions(xmlOptions)
.build();
+ HttpApiConfig mockXmlConfigWithSchema = HttpApiConfig.builder()
+ .url(makeUrl("http://localhost:%d/xml"))
+ .method("GET")
+ .headers(headers)
+ .authType("basic")
+ .userName("user")
+ .password("pass")
+ .dataPath("results")
+ .inputType("xml")
+ .xmlOptions(xmlOptionsWithSchhema)
+ .build();
+
+
HttpApiConfig mockGithubWithParam = HttpApiConfig.builder()
.url(makeUrl("http://localhost:%d/orgs/{org}/repos"))
.method("GET")
@@ -349,6 +397,7 @@
configs.put("mockPostPushdownWithStaticParams", mockPostPushdownWithStaticParams);
configs.put("mockcsv", mockCsvConfig);
configs.put("mockxml", mockXmlConfig);
+ configs.put("mockxml_with_schema", mockXmlConfigWithSchema);
configs.put("github", mockGithubWithParam);
configs.put("github2", mockGithubWithDuplicateParam);
configs.put("github3", mockGithubWithParamInQuery);
@@ -385,6 +434,7 @@
.addRow("local.mockcsv", "http")
.addRow("local.mockpost", "http")
.addRow("local.mockxml", "http")
+ .addRow("local.mockxml_with_schema", "http")
.addRow("local.nullpost", "http")
.addRow("local.sunrise", "http")
.build();
@@ -505,6 +555,35 @@
doSimpleSpecificQuery(sql);
}
+ @Test
+ @Ignore("Requires Remote Server")
+ public void simpleStarQueryWithXMLParams() throws Exception {
+ String sql = "SELECT year, department, expense_category, budget_code, budget_name, modified, adopted " +
+ "FROM live.nyc WHERE type_of_data='Budget' AND records_from=1 AND max_records=5 AND year IS NOT null";
+
+ RowSet results = client.queryBuilder().sql(sql).rowSet();
+
+ TupleMetadata expectedSchema = new SchemaBuilder()
+ .add("year", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .add("department", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .add("expense_category", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .add("budget_code", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .add("budget_name", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .add("modified", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .add("adopted", TypeProtos.MinorType.VARCHAR, TypeProtos.DataMode.OPTIONAL)
+ .build();
+
+ RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema)
+ .addRow("2022", "MEDICAL ASSISTANCE - OTPS", "MEDICAL ASSISTANCE", "9564", "MMIS MEDICAL ASSISTANCE", "5972433142", "5584533142")
+ .addRow("2020", "MEDICAL ASSISTANCE - OTPS", "MEDICAL ASSISTANCE", "9564", "MMIS MEDICAL ASSISTANCE", "5819588142", "4953233142")
+ .addRow("2014", "MEDICAL ASSISTANCE - OTPS", "MEDICAL ASSISTANCE", "9564", "MMIS MEDICAL ASSISTANCE", "5708101276", "5231324567")
+ .addRow("2015", "MEDICAL ASSISTANCE - OTPS", "MEDICAL ASSISTANCE", "9564", "MMIS MEDICAL ASSISTANCE", "5663673673", "5312507361")
+ .build();
+
+ RowSetUtilities.verify(expected, results);
+ }
+
+
private void doSimpleSpecificQuery(String sql) throws Exception {
RowSet results = client.queryBuilder().sql(sql).rowSet();
@@ -759,6 +838,22 @@
}
@Test
+ public void testSerDeXML() throws Exception {
+ try (MockWebServer server = startServer()) {
+
+ server.enqueue(
+ new MockResponse().setResponseCode(200)
+ .setBody(TEST_XML_RESPONSE)
+ );
+
+ String sql = "SELECT COUNT(*) FROM local.mockxml.`xml?arg1=4` ";
+ String plan = queryBuilder().sql(sql).explainJson();
+ long cnt = queryBuilder().physical(plan).singletonLong();
+ assertEquals("Counts should match", 36L, cnt);
+ }
+ }
+
+ @Test
public void testSerDeCSV() throws Exception {
try (MockWebServer server = startServer()) {
@@ -875,6 +970,37 @@
}
@Test
+ public void testXmlWithSchemaResponse() throws Exception {
+ String sql = "SELECT * FROM local.mockxml_with_schema.`?arg1=4` LIMIT 5";
+ try (MockWebServer server = startServer()) {
+
+ server.enqueue(new MockResponse().setResponseCode(200).setBody(TEST_XML_RESPONSE));
+
+ RowSet results = client.queryBuilder().sql(sql).rowSet();
+
+ TupleMetadata expectedSchema = new SchemaBuilder()
+ .add("attributes", MinorType.MAP)
+ .addNullable("COMMON", MinorType.VARCHAR)
+ .addNullable("BOTANICAL", MinorType.VARCHAR)
+ .addNullable("ZONE", MinorType.INT)
+ .addNullable("LIGHT", MinorType.VARCHAR)
+ .addNullable("PRICE", MinorType.VARCHAR)
+ .addNullable("AVAILABILITY", MinorType.VARCHAR)
+ .buildSchema();
+
+ RowSet expected = new RowSetBuilder(client.allocator(), expectedSchema)
+ .addRow(mapArray(), "Bloodroot", "Sanguinaria canadensis", 4, "Mostly Shady", "$2.44", "031599")
+ .addRow(mapArray(),"Columbine", "Aquilegia canadensis", 3, "Mostly Shady", "$9.37", "030699")
+ .addRow(mapArray(),"Marsh Marigold", "Caltha palustris", 4, "Mostly Sunny", "$6.81", "051799")
+ .addRow(mapArray(), "Cowslip", "Caltha palustris", 4, "Mostly Shady", "$9.90", "030699")
+ .addRow(mapArray(), "Dutchman's-Breeches", "Dicentra cucullaria", 3, "Mostly Shady", "$6.44", "012099")
+ .build();
+
+ RowSetUtilities.verify(expected, results);
+ }
+ }
+
+ @Test
public void testImplicitFieldsWithJSON() throws Exception {
String sql = "SELECT _response_code, _response_message, _response_protocol, _response_url FROM local.sunrise.`?lat=36.7201600&lng=-4.4203400&date=2019-10-02`";
try (MockWebServer server = startServer()) {
diff --git a/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestPagination.java b/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestPagination.java
index 2334315..5931e0d 100644
--- a/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestPagination.java
+++ b/contrib/storage-http/src/test/java/org/apache/drill/exec/store/http/TestPagination.java
@@ -226,6 +226,10 @@
List<String> params = new ArrayList<>();
params.add("foo");
+ HttpXmlOptions xmlOptions = HttpXmlOptions.builder()
+ .dataLevel(2)
+ .build();
+
HttpApiConfig mockXmlConfigWithPaginator = HttpApiConfig.builder()
.url("http://localhost:8092/xml")
.method("GET")
@@ -233,7 +237,7 @@
.params(params)
.paginator(pagePaginatorForXML)
.inputType("xml")
- .xmlDataLevel(2)
+ .xmlOptions(xmlOptions)
.build();
HttpApiConfig mockXmlConfigWithPaginatorAndUrlParams = HttpApiConfig.builder()
@@ -243,7 +247,7 @@
.params(params)
.paginator(pagePaginatorForXML)
.inputType("xml")
- .xmlDataLevel(2)
+ .xmlOptions(xmlOptions)
.build();
diff --git a/contrib/storage-http/src/test/resources/data/response.xml b/contrib/storage-http/src/test/resources/data/response.xml
index d9dc3f5..6681266 100644
--- a/contrib/storage-http/src/test/resources/data/response.xml
+++ b/contrib/storage-http/src/test/resources/data/response.xml
@@ -197,7 +197,7 @@
<PLANT>
<COMMON>Black-Eyed Susan</COMMON>
<BOTANICAL>Rudbeckia hirta</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Sunny</LIGHT>
<PRICE>$9.80</PRICE>
<AVAILABILITY>061899</AVAILABILITY>
@@ -221,7 +221,7 @@
<PLANT>
<COMMON>Butterfly Weed</COMMON>
<BOTANICAL>Asclepias tuberosa</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Sunny</LIGHT>
<PRICE>$2.78</PRICE>
<AVAILABILITY>063099</AVAILABILITY>
@@ -229,7 +229,7 @@
<PLANT>
<COMMON>Cinquefoil</COMMON>
<BOTANICAL>Potentilla</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Shade</LIGHT>
<PRICE>$7.06</PRICE>
<AVAILABILITY>052599</AVAILABILITY>
@@ -237,7 +237,7 @@
<PLANT>
<COMMON>Primrose</COMMON>
<BOTANICAL>Oenothera</BOTANICAL>
- <ZONE>3 - 5</ZONE>
+ <ZONE>3</ZONE>
<LIGHT>Sunny</LIGHT>
<PRICE>$6.56</PRICE>
<AVAILABILITY>013099</AVAILABILITY>
@@ -261,7 +261,7 @@
<PLANT>
<COMMON>Jacob's Ladder</COMMON>
<BOTANICAL>Polemonium caeruleum</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Shade</LIGHT>
<PRICE>$9.26</PRICE>
<AVAILABILITY>022199</AVAILABILITY>
@@ -269,7 +269,7 @@
<PLANT>
<COMMON>Greek Valerian</COMMON>
<BOTANICAL>Polemonium caeruleum</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Shade</LIGHT>
<PRICE>$4.36</PRICE>
<AVAILABILITY>071499</AVAILABILITY>
@@ -277,7 +277,7 @@
<PLANT>
<COMMON>California Poppy</COMMON>
<BOTANICAL>Eschscholzia californica</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Sun</LIGHT>
<PRICE>$7.89</PRICE>
<AVAILABILITY>032799</AVAILABILITY>
@@ -285,7 +285,7 @@
<PLANT>
<COMMON>Shooting Star</COMMON>
<BOTANICAL>Dodecatheon</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Mostly Shady</LIGHT>
<PRICE>$8.60</PRICE>
<AVAILABILITY>051399</AVAILABILITY>
@@ -293,7 +293,7 @@
<PLANT>
<COMMON>Snakeroot</COMMON>
<BOTANICAL>Cimicifuga</BOTANICAL>
- <ZONE>Annual</ZONE>
+ <ZONE>8</ZONE>
<LIGHT>Shade</LIGHT>
<PRICE>$5.63</PRICE>
<AVAILABILITY>071199</AVAILABILITY>
@@ -306,4 +306,4 @@
<PRICE>$3.02</PRICE>
<AVAILABILITY>022299</AVAILABILITY>
</PLANT>
-</CATALOG>
\ No newline at end of file
+</CATALOG>
