Support sqlite's CREATE VIRTUAL TABLE

[chaozwn]

Describe the feature

When I am using the feature to create external tables, I want to create an external table through JDBC.

1. add source.yml

version: 2

sources:
  - name: sqlite
    tables:
      - name: raw_jayce
        external:
          using: jdbc
          options:
            url: "jdbc:sqlite:./database/main.db"
            driver: "org.sqlite.JDBC"
            dbtable: "main.raw_jayce"

2. compile

dbt run-operation stage_external_sources --vars "ext_full_refresh: true"

3. found warning

18:11:09  1 of 1 (1) create schema if not exists sqlite
18:11:09  1 of 1 (1) OK
18:11:09  1 of 1 (2) drop table if exists sqlite.raw_jayce
02:11:09.873 [Thread-4] ERROR org.apache.hadoop.hive.common.FileUtils - Failed to delete file:/Users/zhaown/workspace/dbt_project/jaffle_shop/spark-warehouse/sqlite.db/raw_jayce
18:11:09  1 of 1 (2) OK
18:11:09  1 of 1 (3) create table sqlite.raw_jayce using jdbc    options ('url' = 'jdbc:sqlite:./data...  
**24/04/14 02:11:09 WARN HiveExternalCatalog: Couldn't find corresponding Hive SerDe for data source provider jdbc. Persisting data source table `spark_catalog`.`sqlite`.`raw_jayce` into Hive metastore in Spark SQL specific format, which is NOT compatible with Hive.**
18:11:10  1 of 1 (3) OK

Describe alternatives you've considered

add materialized: view, and freely choose between "CREATE TABLE" and "CREATE VIEW".

[dataders]

am I correct that you'd like to add support for creating external tables with SQLite?

My guess is that dbt-external-tables is not compatible "out-of-the-box" with dbt-sqlite. This isn't possible today with, but a quick Google tells me the correct syntax is CREATE VIRTUAL TABLE

This package certainly could be extended to support sqlite. Happy to help with this. For anyone who is interested, I'd start by looking at the macros in the macros/plugins/ directory.