数据整合处理的工具，apache-drill

# Generic REST API Storage Plugin The HTTP storage plugin lets you query APIs over HTTP/REST. The plugin expects JSON responses. The HTTP plugin is new in Drill 1.18 and is an Alpha feature. It works well, and we encourage you to use it and provide feedback. However, we reserve the right to change the plugin based on that feedback. ## Configuration To configure the plugin, create a new storage plugin, and add the following configuration options which apply to ALL connections defined in this plugin: ```json { "type": "http", "cacheResults": true, "connections": {}, "timeout": 0, "proxyHost": null, "proxyPort": 0, "proxyType": null, "proxyUsername": null, "proxyPassword": null, "enabled": true } ``` The required options are: * `type`: This should be `http` * `cacheResults`: Enable caching of the HTTP responses. Defaults to `false` * `timeout`: Sets the response timeout in seconds. Defaults to `0` which is no timeout. * `connections`: This field contains the details for individual connections. See the section *Configuring API Connections for Details*. You can configure Drill to work behind a corporate proxy. Details are listed below. ### Configuring the API Connections The HTTP Storage plugin allows you to configure multiple APIS which you can query directly from this plugin. To do so, first add a `connections` parameter to the configuration . Next give the connection a name, which will be used in queries. For instance `stockAPI` or `jira`. The `connection` property can accept the following options. #### URL `url`: The base URL which Drill will query. ##### Parameters in the URL 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 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 * 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. #### 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. * All URL parameter clauses must be equality only. ### Passing Parameters in the Query `requireTail`: Set to `true` if the query must contain an additional part of the service URL as a table name, `false` if the URL needs no additional suffix other than that provided by `WHERE` clause filters. (See below.) If your service requires parameters, you have three choices. Suppose your connection is called `sunrise`. First, can include them directly in your URL if the parameters a fixed for a given service: ```json url: "https://api.sunrise-sunset.org/json?lat=36.7201600&lng=-4.4203400&date=2019-10-02", requireTail: false ``` Query your table like this: ```sql SELECT * FROM api.sunrise; ``` Second, you can specify the base URL here and the full URL in your query. Use this form if the parameters define a table-like concept (the set of data to return): ```json url: "https://api.sunrise-sunset.org/json", requireTail: true ``` SQL query: ```sql SELECT * FROM api.sunrise.`?lat=36.7201600&lng=-4.4203400&date=2019-10-02` ``` If the URL requires a tail, specify it as if it were a table name. (See example below.) Drill directly appends the "tail" to the base URL to create the final URL. Third, you can use the `params` field below to specify the parameters as filters if the parameters specify which data sets to return: ```json url: "https://api.sunrise-sunset.org/json", requireTail: false, params: ["lat", "lng", "date"] ``` SQL query: ```sql SELECT * FROM api.sunrise WHERE `lat` = 36.7201600 AND `lng` = -4.4203400 AND `date` = '2019-10-02' ``` In this case, Drill appends the parameters to the URL, adding a question mark to separate the two. #### Method `method`: The request method. Must be `GET` or `POST`. Other methods are not allowed and will default to `GET`. `postBody`: Contains data, in the form of key value pairs, which are sent during a `POST` request. The post body should be in the of a block of text with key/value pairs: ```json postBody: "key1=value1 key2=value2" ``` `postBodyLocation`: If the API uses the `POST` method, you can send parameters in several different ways: * `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 `headers`: Often APIs will require custom headers as part of the authentication. This field allows you to define key/value pairs which are submitted with the http request. The format is: ```json headers: { "key1": "Value1", "key2": "Value2" } ``` #### Query Parmeters as Filters * `params`: Allows you to map SQL `WHERE` clause conditions to query parameters. ```json url: "https://api.sunrise-sunset.org/json", params: ["lat", "lng", "date"] ``` SQL query: ```sql SELECT * FROM api.sunrise WHERE `lat` = 36.7201600 AND `lng` = -4.4203400 AND `date` = '2019-10-02' ``` HTTP parameters are untyped; Drill converts any value you provide into a string. Drill allows you to use any data type which can convert unambiguously to a string: `BIT`, `INT`, `BIGINT`, `FLOAT4`, `FLOAT8`, `VARDECIMAL`, `VARCHAR`. The `BIT` type is translated to `true` and `false`. Note that none of the date or interval types are allowed: each of those requires formatting. Note the need for back-tick quotes around the names; `date` is a reserved word. Notice also that the date is a string because of the formatting limitation mentioned above. Only equality conditions can be translated to parameters. The above filters are translated to: ``` lat=36.7201600&lng=-4.4203400&date=2019-10-02 ``` If your query contains other conditions (`!=`, `<`, etc.) then those conditions are applied in Drill after the REST service returns the data. Only fields listed in the `params` config filed will become parameters, all other expressions are handled within Drill as explained above. At present, Drill requires the values to be literals (constants). Drill does not currently allow expressions. That is, the following will not become an HTTP parameter: ```sql WHERE `lat` = 36 + 0.7201600 ``` Drill will add parameters to the URL in the order listed in the config. Use this feature if the API is strict about parameter ordering. At present Drill does not enforce that parameters are provided in the query: Drill assumes parameters are optional. ### Data Path REST responses often have structure beyond the data you want to query. For example: ```json "results": { "sunrise":"7:27:02 AM", "sunset":"5:05:55 PM", "solar_noon":"12:16:28 PM", "day_length":"9:38:53", "civil_twilight_begin":"6:58:14 AM", "civil_twilight_end":"5:34:43 PM", "nautical_twilight_begin":"6:25:47 AM", "nautical_twilight_end":"6:07:10 PM", "astronomical_twilight_begin":"5:54:14 AM", "astronomical_twilight_end":"6:38:43 PM" }, "status":"OK" } ``` Drill can handle JSON structures such as the above; you can use SQL to