How to Create an ARP Connector

How to Create an ARP Connector

• Storage plugin configuration: [data source]Conf.java
• Plugin ARP YAML file: [data source].arp.yaml

The storage plugin configuration file tells Dremio what the name of the plugin should be, what connection options should be displayed in the source UI such as host address, user credentials, etc., what the name of the ARP file is, which JDBC driver to use and how to make a connection to the JDBC driver.

The Plugin ARP YAML file is what is used to modify the SQL queries that are sent to the JDBC driver, allowing you to specify support for different data types and functions, as well as rewrite them if tweaks need to be made for your specific data source.

Metadata: This section outlines some high level metadata about the plugin.

Syntax: This section allows for specifying some general syntax items like the identifier quote character.

Data Types: This section outlines which data types are supported by the plugin, their names as they appear in the JDBC driver, and how they map to Dremio types.

Relational Algebra: This section is divided up into a number of other subsections:

• Aggregation: Specify what aggregate functions, such as SUM, MAX, etc, are supported and what signatures they have. You can also specify a rewrite to alter the SQL for how this is issued.
• except/project/join/sort/union/union_all/values: These sections indicate if the specific operation is supported or not.
• Expressions: This section outlines general operations that are supported. The main sections are:
• Operators: Outlines which scalar functions, such as SIN, SUBSTR, LOWER, etc, are supported, along with the signatures of these functions which are supported. Finally, you can also specify a rewrite to alter the SQL for how this is issued.
• Variable_length_operators: The same as operators, but allows specification of functions which may have a variable number of arguments, such as AND and OR.

If an operation or function is not specified in the ARP file, then Dremio will handle the operation itself. Any operations which are indicated as supported but need to be stacked on operations which are not will not be pushed down to the SQL query.

Line 38

@SourceType(value = "SQLITE", label = "SQLite")

Line 39

public class SqliteConf extends AbstractArpConf<SqliteConf> {

And Line 40

private static final String ARP_FILENAME = "arp/implementation/sqlite-arp.yaml";

In addition to these lines, we have indicated the JDBC driver class for the driver that we will use in line 43:

Note that this driver class is different for each data source, you will need to look up in your source’s documentation to see what is the driver class for that source.

private static final String DRIVER = "org.sqlite.JDBC";

@NotBlank
  @Tag(1)
  @DisplayMetadata(label = "Database")
  public String database;
@Tag(2)
  @DisplayMetadata(label = "Record fetch size")
  @NotMetadataImpacting
  public int fetchSize = 200;

These lines will be responsible for the following items in the GUI

By default, each field is considered “metadata impacting”, which means that a change of the value of that field could result in different metadata that Dremio will see. For example, the service account username would be metadata impacting because different users could have different permissions and therefore see different objects. The @NotMetadataImpacting tag tells Dremio that this field is not considered “metadata impacting”.

@VisibleForTesting
  public String toJdbcConnectionString() {
    final String database = checkNotNull(this.database, "Missing database.");
    return String.format("jdbc:sqlite:%s", database);

Save and close the file.

metadata:
  # Manually Configured Metadata Section.
  name: SQLITE
  apiname: sqlite
  spec_version: '1'

The syntax section allows for specifying some general syntax items like the identifier quote character.

syntax:
  # Manually Configured Syntax Section.
  identifier_quote: '"'
  identifier_length_limit: 128
  allows_boolean_literal: false
  map_boolean_literal_to_bit: false
  supports_catalogs: false
  supports_schemas: false

Now we can move on to the data_types section, this part of the file outlines which data types are supported by the plugin, their names as they appear in the JDBC driver, and how they map to Dremio types. You will need to refer to your data source’s documentation to complete the mapping.

First we will do the basic mappings for numeric types, date types and string types. We will list some of them here, you can refer to the final YAML file to see all of them.

Data types:

data_types:
  mappings:
  # Manually Configured Data Types Mappings Section.
  - source:
      name: "INTEGER"
    dremio:
      name: "integer"
    required_cast_arguments: "none"
  - source:
      name: "REAL"
    dremio:
      name: "double"
    required_cast_arguments: "none"
  - source:
      name: "VARCHAR"
    dremio:
      name: "varchar"
    required_cast_arguments: "none"

• Pom.xml
• Readme.md
• Src (folder) ->Main->Resources->Arp->Implementation->sqlite-arp.yaml
• Scr->Main->Java->Com->dremio->exec->store->jdbc->conf->SqliteConf.java

Now, verify that Maven is installed on your computer. Once ready using the terminal, navigate to the directory where the pom, readme and src files are located and run the following command:

mvn clean install

After a few minutes, you will see a Build Success message on the terminal, and also there will be a Target directory created in your current working path.

If there are any errors in the build, Maven will display the exact location of the issue so you can troubleshoot it, once ready simply save the file and run the build command again.

Select SQLite from the list of available sources.

Add the connection and authentication parameters and click save.

If everything went well, you should be able to see the directories inside your data source.

Thankfully, Maven is good about pinpointing the coordinates (line and character, issue, and file causing it) of where the issue is located.

These are some of the things to look for in the [your source]Conf.java file:

• Double check that the private static final String DRIVER = contains the correct JDBC class for the data source that you are working with.
• Check that the @DisplayMetadata variable names (username, password, etc) are uniform across the entire file.
• Make sure that the JDBC string is correctly built

<logger name="com.dremio.exec.store.jdbc">
    <level value="${dremio.log.level:-trace}"/>
</logger>

After running queries, you might notice lines in the server.log file like the following:

- 2019-07-11 18:56:24,001 [22d879a7-ce3d-f2ca-f380-005a88865700/0:foreman-planning] DEBUG c.d.e.store.jdbc.dialect.arp.ArpYaml - Operator / not supported. Aborting pushdown.

To troubleshoot aborted pushdowns, always refer to the [your source]-arp.yaml and double check your data source’s documentation to make sure the supported SQL functions are listed correctly.