What would you like to be added:

CREATE OR REPLACE TABLE PROD_TEMP.TEMP.booleantest (iscool boolean)

2019-08-13 20:00:55,163 [22ace908-d877-e0ab-fa46-de0ece93fd00/0:foreman-planning] WARN  c.d.e.store.jdbc.dialect.TypeMapper - A column you queried has a data type that is not currently supported by the JDBC storage plugin. The column's name was PROD_TEMP.TEMP.BOOLEANTEST.ISCOOL,
 its JDBC data type was BOOLEAN, and the source column type was BOOLEAN.
2019-08-13 20:00:55,165 [22ace908-d877-e0ab-fa46-de0ece93fd00/0:foreman-planning] INFO  c.d.e.store.jdbc.JdbcDatasetMetadata - Took 336 ms to get column metadata for [sf-dremio, PROD_TEMP, TEMP, BOOLEANTEST]
...
2019-08-13 20:00:55,610 [22ace908-d877-e0ab-fa46-de0ece93fd00/0:foreman-planning] INFO  com.dremio.exec.record.BatchSchema - User Error Occurred [ErrorId: 88568829-c2e7-4181-8145-bef98e6e1c0b]
com.dremio.common.exceptions.UserException: Selected table has no columns.
        at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:773) ~[dremio-common-3.2.4-201906051751050278-1bcce62.jar:3.2.4-201906051751050278-1bcce62]
        at com.dremio.exec.record.BatchSchema.toCalciteRecordType(BatchSchema.java:232) [dremio-sabot-kernel-3.2.4-201906051751050278-1bcce62.jar:3.2.4-201906051751050278-1bcce62]
        at com.dremio.exec.store.NamespaceTable.getRowType(NamespaceTable.java:66) [dremio-sabot-kernel-3.2.4-201906051751050278-1bcce62.jar:3.2.4-201906051751050278-1bcce62]

Why is this needed:
boolean column type is used in some cases in production environment.

@narendrans

What happened:
Dremio services fails to start after upgrading to v23.0.1 and rebuilding the snowflake connectors to v23.0.1

What you expected to happen:
It should be working fine after upgrading both dremio version and snowflake connectors to v23.0.1

How to reproduce it (as minimally and precisely as possible):
Upgrade Dremio to v23.0.1 and update the connectors to v23.0.1, restart dremio coordinators, executors.

Anything else we need to know?:
Error from server.log
2022-12-19 07:25:59,456 [main] ERROR ROOT - Dremio is exiting. Failure while starting services.
java.lang.RuntimeException: Reading from a byte array threw an IOException (should never happen).
at io.protostuff.IOUtil.mergeFrom(IOUtil.java:54)
at io.protostuff.ProtobufIOUtil.mergeFrom(ProtobufIOUtil.java:103)
at com.dremio.exec.catalog.ConnectionReaderImpl.getConnectionConf(ConnectionReaderImpl.java:118)
at com.dremio.exec.catalog.ConnectionReaderImpl.getConnectionConf(ConnectionReaderImpl.java:125)
at com.dremio.exec.catalog.ManagedStoragePlugin.(ManagedStoragePlugin.java:173)
at com.dremio.exec.catalog.EnterpriseManagedStoragePlugin.(EnterpriseManagedStoragePlugin.java:64)
at com.dremio.exec.catalog.EnterprisePluginsManager.newManagedStoragePlugin(EnterprisePluginsManager.java:69)
at com.dremio.exec.catalog.PluginsManager.newPlugin(PluginsManager.java:280)
at com.dremio.exec.catalog.PluginsManager.start(PluginsManager.java:224)
at com.dremio.exec.catalog.CatalogServiceImpl.start(CatalogServiceImpl.java:216)
at com.dremio.service.SingletonRegistry$AbstractServiceReference.start(SingletonRegistry.java:139)
at com.dremio.service.ServiceRegistry.start(ServiceRegistry.java:88)
at com.dremio.service.SingletonRegistry.start(SingletonRegistry.java:33)
at com.dremio.dac.daemon.DACDaemon.startServices(DACDaemon.java:199)
at com.dremio.dac.daemon.DACDaemon.init(DACDaemon.java:205)
at com.dremio.dac.daemon.DremioDaemon.main(DremioDaemon.java:104)
Suppressed: java.lang.NullPointerException: null
at com.dremio.service.orphanagecleaner.OrphanageCleanerService.close(OrphanageCleanerService.java:124)
at com.dremio.service.SingletonRegistry$AbstractServiceReference.close(SingletonRegistry.java:132)
at com.dremio.common.AutoCloseables.close(AutoCloseables.java:139)
at com.dremio.service.ServiceRegistry.close(ServiceRegistry.java:96)
at com.dremio.service.SingletonRegistry.close(SingletonRegistry.java:90)
at com.dremio.common.AutoCloseables.close(AutoCloseables.java:139)
at com.dremio.common.AutoCloseables.close(AutoCloseables.java:76)
at com.dremio.dac.daemon.DACDaemon.close(DACDaemon.java:299)
at com.dremio.dac.daemon.DremioDaemon.main(DremioDaemon.java:103)
Suppressed: java.lang.NullPointerException: null
at com.dremio.provision.service.ProvisioningServiceImpl.close(ProvisioningServiceImpl.java:512)
... 8 common frames omitted
Caused by: io.protostuff.ProtobufException: CodedInput encountered an embedded string or bytes that misreported its size.
at io.protostuff.ProtobufException.misreportedSize(ProtobufException.java:86)
at io.protostuff.ByteArrayInput.readString(ByteArrayInput.java:438)
at io.protostuff.runtime.RuntimeUnsafeFieldFactory$9$1.mergeFrom(RuntimeUnsafeFieldFactory.java:753)
at io.protostuff.runtime.RuntimeSchema.mergeFrom(RuntimeSchema.java:463)
at io.protostuff.CustomSchema.mergeFrom(CustomSchema.java:57)
at io.protostuff.IOUtil.mergeFrom(IOUtil.java:45)
... 15 common frames omitted

Details:

• Dremio version (Help > About from the UI): 23.0.1-202210141019030815-c1de8bcc
• Profile zip:
• server.log:
  server.log

What would you like to be added:
There are a handful of SQL functions/operators that I would like to see added to the snowflake-arp.yaml file. I have added several that are commonly used by my team and will be submitting a PR shortly. I have tested each addition and examined the query profile details to ensure it now gets pushed down.

Why is this needed:
If the names/signatures don't exist in the snowflake-arp.yaml file, then predicates that use the function don't get pushed down. My team utilizes snowflake extensively; predicate pushdown is critical for queries to be more performant.

@narendrans - I see that there was a release in November supporting Dremio v23.

But, I see that there is going to be an official Snowflake Plugin from Dremio starting v23 & looks like they are not going to support the Community Plug-in anymore.

I just wanted to check with you to see if there would be anymore updates for the Community Plug-in.

Appreciate your response. Thanks !

What would you like to be added:

Currently, I cannot request json-variants (like the waether-snowflake-testdataset). I get the error-message Failure while attempting to read from database.

Is there a way I do not see right now?

Thanks! :)

PASSED; FULL PUSH DOWN

select
  s.s_acctbal,
  s.s_name,
  n.n_name,
  p.p_partkey,
  p.p_mfgr,
  s.s_address,
  s.s_phone,
  s.s_comment
from
  "part" p,
  "supplier" s,
  "partsupp" ps,
  "nation" n,
  "region" r
where
  p.p_partkey = ps.ps_partkey
  and s.s_suppkey = ps.ps_suppkey
  and p.p_size = 41
  and p.p_type like '%NICKEL'
  and s.s_nationkey = n.n_nationkey
  and n.n_regionkey = r.r_regionkey
  and r.r_name = 'EUROPE'
  and ps.ps_supplycost = (

    select
      min(ps.ps_supplycost)

    from
      "partsupp" ps,
      "supplier" s,
      "nation" n,
      "region" r
    where
      p.p_partkey = ps.ps_partkey
      and s.s_suppkey = ps.ps_suppkey
      and s.s_nationkey = n.n_nationkey
      and n.n_regionkey = r.r_regionkey
      and r.r_name = 'EUROPE'
  )

order by
  s.s_acctbal desc,
  n.n_name,
  s.s_name,
  p.p_partkey
limit 100

What happened: We are running on Dremio Enterprise v20.2.3. We use Snowflake ARP connector for Snowflake connectivity (dremio-snowflake-plugin-20.1.0.jar + snowflake-jdbc-3.13.5.jar) and use Snowflake Service Account to connect to those warehouse.

We have been notified by Snowflake team that every Snowflake Source in Dremio is creating sessions every 60 secs in Snowflake environment and we are trying to understand if this is the expected behavior. Note, these are not the Metadata refresh that Dremio does every 1 Hour for dataset discovery & dataset details fetch.

What you expected to happen: We would like to know if there is a way to stop this behavior. At this point am not sure if these sessions are consuming any Snowflake resources or not.

How to reproduce it (as minimally and precisely as possible): NA

Anything else we need to know?: Second part to this ticket is, Metadata refresh is essentially consuming Snowflake resources - Are there any ways to prevent it except increasing the Metadata Refresh frequency!

Details:

• Dremio version (Help > About from the UI): 20.2.3
• Profile zip:
• server.log:

What would you like to be added:
It would be nice to be able to take some action if the jdbc connection failed. Either on initial setup or at the time of query.
Why is this needed:
For example, if the jdbc connection fails because the username/password has changed on the source database (policy to roll passwords periodically), it would be nice to be able to execute code that would enable us to re-fresh the credential from an external source of username/passwords (a password vault).

What happened:
Not able to flatten varaint column type of snowflake in dremio

What you expected to happen:

Once the table is imported as a pds and a vds is created converting variant column to json, I expect to extract elements from the column as I would do with any other source type containing json
How to reproduce it (as minimally and precisely as possible):
a) Create a table in snowflake with variant type column
b) Import that table as PDS in dremio. As per the yaml, the variant column is recognized as varchar in dremio
c) Use function convert_from to recognize that column as json and save as a vds
d) Now start extracting elements (through UI or command) from the vds created in step c and you will encounter an error (Using CONVERT_FROM(*, 'JSON') is only supported against string literals and direct table references of types VARCHAR and VARBINARY.

Anything else we need to know?:

Details: Please slack me if you need repro details
dremio_issue.zip
35540b76-fa41-4b95-afc3-95fcedd3f9f1.zip

• Dremio version (Help > About from the UI):
• Profile zip:
• server.log:

What happened:
Recently we have started using Dremio(Data as Service) and tried connecting to Snowflake.

What you expected to happen:

While connecting to Snowflake from Dremio and it is throwing following error message

ERROR Unavailable: Unable to find handler for source of type [SNOWFLAKE]

SOURCE_BAD_STATE ERROR: The source [] is currently unavailable. Info: [Unavailable: Unable to find handler for source of type [SNOWFLAKE].]

ERROR Unavailable: Unable to find handler for source of type [SNOWFLAKE].

Fragment 1:0

[Error Id: 1fefea06-ec0a-42e9-bb76-07433574466d on :-1]

ERROR Unavailable: Unable to find handler for source of type [SNOWFLAKE].

Fragment 1:0

How to reproduce it (as minimally and precisely as possible):
Use Dremio(Data as Service) and try connecting to snowflake using available connectors

Anything else we need to know?:

Details:

• Dremio version (Help > About from the UI): Dremio 3.0
• Profile zip:
• server.log:

What would you like to be added:

Why is this needed:

What happened:
After using the plugin successfully for a week or so all queries started failing against Snowflake. When we looked at the Job Profile we were able to see the following error from the Snowflake JDBC driver: (net.snowflake.client.jdbc.SnowflakeReauthenticationRequest) Authentication token has expired. The user must authenticate again.

What you expected to happen:
Reauthentication would be handled automatically and queries would continue to work.

How to reproduce it (as minimally and precisely as possible):
Don't restart your executors very often? Not sure.

Anything else we need to know?:
We are running all of our executors provisioned on YARN. Restarting Dremio did not resolve this error, but restarting the provisioned executors did. This makes sense, since the Snowflake authentication was actually being performed on the executors. Full stack trace of the error follows:

  DATA_READ ERROR: The JDBC storage plugin failed while trying setup the SQL query. 

sql SELECT *
FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS" LIMIT 2000 OFFSET 0
plugin sf2
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:6
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:6
Fragment 0:0

[Error Id: 41bd1430-0393-4872-b04f-b8b2411b463c on {servername}:-1]

(net.snowflake.client.jdbc.SnowflakeReauthenticationRequest) Authentication token has expired. The user must authenticate again.
net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionSub():135
net.snowflake.client.jdbc.SnowflakeUtil.checkErrorAndThrowExceptionIncludingReauth():58
net.snowflake.client.core.SessionUtil.tokenRequest():909
net.snowflake.client.core.SessionUtil.renewSession():775
net.snowflake.client.core.SFSession.renewSession():690
net.snowflake.client.core.SFStatement.executeHelper():512
net.snowflake.client.core.SFStatement.executeQueryInternal():247
net.snowflake.client.core.SFStatement.executeQuery():186
net.snowflake.client.core.SFStatement.execute():790
net.snowflake.client.jdbc.SnowflakeStatementV1.executeQueryInternal():245
net.snowflake.client.jdbc.SnowflakeStatementV1.executeQuery():166
org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
org.apache.commons.dbcp2.DelegatingStatement.executeQuery():206
com.dremio.exec.store.jdbc.JdbcRecordReader.setup():197
com.dremio.exec.store.CoercionReader.setup():109
com.dremio.sabot.op.scan.ScanOperator$1.run():220
com.dremio.sabot.op.scan.ScanOperator$1.run():216
java.security.AccessController.doPrivileged():-2
javax.security.auth.Subject.doAs():422
org.apache.hadoop.security.UserGroupInformation.doAs():1836
com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():216
com.dremio.sabot.op.scan.ScanOperator.setupReader():189
com.dremio.sabot.op.scan.ScanOperator.setup():175
com.dremio.sabot.driver.SmartOp$SmartProducer.setup():558
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
com.dremio.sabot.driver.SmartOp$SmartProducer.accept():528
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.StraightPipe.setup():102
com.dremio.sabot.driver.Pipeline.setup():68
com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():360
com.dremio.sabot.exec.fragment.FragmentExecutor.run():244
com.dremio.sabot.exec.fragment.FragmentExecutor.access$1200():92
com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():632
com.dremio.sabot.task.AsyncTaskWrapper.run():104
com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():226
com.dremio.sabot.task.slicing.SlicingThread.run():156

Details:

• Dremio version (Help > About from the UI): 3.2.4
• Profile zip:
• server.log:

Hi Naren/Team,

I see that we have snowflake plugin jars for different versions. And latest one is for Dremio v17.

What would you like to be added: I would like to setup connectivity to Snowflake from Dremio v19.1 with basic authentication(username and password).

Why is this needed: I need this so that we can query on Snowflake data from Dremio.

Thanks,
Janani

What happened:
When previewing or limiting data, the limit appears to happen locally.

What you expected to happen:
Limit pushdown

How to reproduce it (as minimally and precisely as possible):
Latest version of SF plugin with Dremio 4.1.8.

Anything else we need to know?:

Details:

• Dremio version (Help > About from the UI): 4.1.8

Job Plan
00-00 Screen : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 10000.0, cumulative cost = {3071000.0 rows, 1.351411374E8 cpu, 1.35E8 io, 3.8076E8 network, 0.0 memory}, id = 12903
00-01 Project(Fragment=[$0], Records=[$1], Path=[$2], Metadata=[$3], Partition=[$4], FileSize=[$5]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 10000.0, cumulative cost = {3070000.0 rows, 1.351401374E8 cpu, 1.35E8 io, 3.8076E8 network, 0.0 memory}, id = 12902
00-02 WriterCommitter(final=[/dremio/results/2158c827-9073-36f6-ec60-c53216909d00]) : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 10000.0, cumulative cost = {3060000.0 rows, 1.351401368E8 cpu, 1.35E8 io, 3.8076E8 network, 0.0 memory}, id = 12901
00-03 UnionExchange : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 10000.0, cumulative cost = {3050000.0 rows, 1.351301368E8 cpu, 1.35E8 io, 3.8076E8 network, 0.0 memory}, id = 12900
01-01 Writer : rowType = RecordType(VARCHAR(65536) Fragment, BIGINT Records, VARCHAR(65536) Path, VARBINARY(65536) Metadata, INTEGER Partition, BIGINT FileSize): rowcount = 10000.0, cumulative cost = {3040000.0 rows, 1.350501368E8 cpu, 1.35E8 io, 1.35E8 network, 0.0 memory}, id = 12899
01-02 Project(O_ORDERKEY=[$0], O_CUSTKEY=[$1], O_ORDERSTATUS=[$2], O_TOTALPRICE=[$3], O_ORDERDATE=[$4], O_ORDERPRIORITY=[$5], O_CLERK=[$6], O_SHIPPRIORITY=[$7], O_COMMENT=[$8]) : rowType = RecordType(DECIMAL(38, 0) O_ORDERKEY, DECIMAL(38, 0) O_CUSTKEY, VARCHAR(65536) O_ORDERSTATUS, DECIMAL(12, 2) O_TOTALPRICE, DATE O_ORDERDATE, VARCHAR(65536) O_ORDERPRIORITY, VARCHAR(65536) O_CLERK, DECIMAL(38, 0) O_SHIPPRIORITY, VARCHAR(65536) O_COMMENT): rowcount = 10000.0, cumulative cost = {3030000.0 rows, 1.350401368E8 cpu, 1.35E8 io, 1.35E8 network, 0.0 memory}, id = 12898
01-03 Project(O_ORDERKEY=[$0], O_CUSTKEY=[$1], O_ORDERSTATUS=[$2], O_TOTALPRICE=[$3], O_ORDERDATE=[$4], O_ORDERPRIORITY=[$5], O_CLERK=[$6], O_SHIPPRIORITY=[$7], O_COMMENT=[$8]) : rowType = RecordType(DECIMAL(38, 0) O_ORDERKEY, DECIMAL(38, 0) O_CUSTKEY, VARCHAR(65536) O_ORDERSTATUS, DECIMAL(12, 2) O_TOTALPRICE, DATE O_ORDERDATE, VARCHAR(65536) O_ORDERPRIORITY, VARCHAR(65536) O_CLERK, DECIMAL(38, 0) O_SHIPPRIORITY, VARCHAR(65536) O_COMMENT): rowcount = 10000.0, cumulative cost = {3020000.0 rows, 1.350401359E8 cpu, 1.35E8 io, 1.35E8 network, 0.0 memory}, id = 12897
01-04 Limit(offset=[0], fetch=[10000]) : rowType = RecordType(DECIMAL(38, 0) O_ORDERKEY, DECIMAL(38, 0) O_CUSTKEY, VARCHAR(65536) O_ORDERSTATUS, DECIMAL(12, 2) O_TOTALPRICE, DATE O_ORDERDATE, VARCHAR(65536) O_ORDERPRIORITY, VARCHAR(65536) O_CLERK, DECIMAL(38, 0) O_SHIPPRIORITY, VARCHAR(65536) O_COMMENT): rowcount = 10000.0, cumulative cost = {3010000.0 rows, 1.35040135E8 cpu, 1.35E8 io, 1.35E8 network, 0.0 memory}, id = 12896
01-05 Project(O_ORDERKEY=[$0], O_CUSTKEY=[$1], O_ORDERSTATUS=[$2], O_TOTALPRICE=[$3], O_ORDERDATE=[$4], O_ORDERPRIORITY=[$5], O_CLERK=[$6], O_SHIPPRIORITY=[$7], O_COMMENT=[$8]) : rowType = RecordType(DECIMAL(38, 0) O_ORDERKEY, DECIMAL(38, 0) O_CUSTKEY, VARCHAR(65536) O_ORDERSTATUS, DECIMAL(12, 2) O_TOTALPRICE, DATE O_ORDERDATE, VARCHAR(65536) O_ORDERPRIORITY, VARCHAR(65536) O_CLERK, DECIMAL(38, 0) O_SHIPPRIORITY, VARCHAR(65536) O_COMMENT): rowcount = 1500000.0, cumulative cost = {3000000.0 rows, 1.35000135E8 cpu, 1.35E8 io, 1.35E8 network, 0.0 memory}, id = 12895
01-06 Jdbc(sql=[SELECT "ORDERS"."O_ORDERKEY", "ORDERS"."O_CUSTKEY", "ORDERS"."O_ORDERSTATUS", "ORDERS"."O_TOTALPRICE", "ORDERS"."O_ORDERDATE", "ORDERS"."O_ORDERPRIORITY", "ORDERS"."O_CLERK", "ORDERS"."O_SHIPPRIORITY", "ORDERS"."O_COMMENT"
FROM "SNOWFLAKE_SAMPLE_DATA"."TPCH_SF1"."ORDERS"]) : rowType = RecordType(DECIMAL(38, 0) O_ORDERKEY, DECIMAL(38, 0) O_CUSTKEY, VARCHAR(65536) O_ORDERSTATUS, DECIMAL(12, 2) O_TOTALPRICE, DATE O_ORDERDATE, VARCHAR(65536) O_ORDERPRIORITY, VARCHAR(65536) O_CLERK, DECIMAL(38, 0) O_SHIPPRIORITY, VARCHAR(65536) O_COMMENT): rowcount = 1500000.0, cumulative cost = {1500000.0 rows, 1.35E8 cpu, 1.35E8 io, 1.35E8 network, 0.0 memory}, id = 12805

What would you like to be added:Please add support for TIMESTAMP_NTZ(9)

2019-08-10 20:59:43,604 [qtp1781910735-166] WARN  c.d.e.store.jdbc.dialect.TypeMapper - A column you queried has a data type that is not currently supported by the JDBC storage plugin. The column's name was FOO.BAR.BAZ.REVISION_DATETIME, its 
JDBC data type was TIMESTAMP, and the source column type was TIMESTAMPNTZ.

Why is this needed:
We have many columns in or repository that are of type:
TIMESTAMP_NTZ(9)

These columns are not returned by the driver and are not available for queries and reporting.

PASSED; FULL PUSH DOWN

select
  l_returnflag,
  l_linestatus,
  sum(l_quantity) as sum_qty,
  sum(l_extendedprice) as sum_base_price,
  sum(l_extendedprice * (1 - l_discount)) as sum_disc_price,
  sum(l_extendedprice * (1 - l_discount) * (1 + l_tax)) as sum_charge,
  avg(l_quantity) as avg_qty,
  avg(l_extendedprice) as avg_price,
  avg(l_discount) as avg_disc,
  count(*) as count_order
from
lineitem
where
  l_shipdate <= date '1998-12-01' - interval '120' day (3)
group by
  l_returnflag,
  l_linestatus
order by
  l_returnflag,
  l_linestatus

What would you like to be added:

A description where the Snowflake-Warehouse (and the user-role) can be specified.

Thanks

What happened: DQLs are failing

What you expected to happen:

How to reproduce it (as minimally and precisely as possible):

1. Install Dremio from AWS marketplace (we tried v13.2)
2. Install the plugin
3. Connect to underlying Snowflake
4. Query editor run DQL

Anything else we need to know?: We tried this in Dremio v12 standalone version and it works

Details:

• Dremio version (Help > About from the UI):13.2 (from AWS Marketplace)
• Profile zip:
• server.log:
  2021-03-02 10:41:45,498 [grpc-default-executor-2] INFO c.d.service.jobs.JobResultsStore - User Error Occurred [ErrorId: d9a52e9b-808f-47d3-aaa8-32fca069ebd8]
  com.dremio.common.exceptions.UserException: Failure getting source
  at com.dremio.common.exceptions.UserException$Builder.build(UserException.java:804)
  at com.dremio.service.jobs.JobResultsStore.loadJobData(JobResultsStore.java:145)
  at com.dremio.service.jobs.JobResultsStore$LateJobLoader.load(JobResultsStore.java:294)
  at com.dremio.service.jobs.JobDataImpl.range(JobDataImpl.java:46)
  at com.dremio.service.jobs.LocalJobsService.getJobData(LocalJobsService.java:906)
  at com.dremio.service.jobs.JobsFlightProducer.getStream(JobsFlightProducer.java:76)
  at org.apache.arrow.flight.FlightService.doGetCustom(FlightService.java:111)
  at org.apache.arrow.flight.FlightBindingService$DoGetMethod.invoke(FlightBindingService.java:144)
  at org.apache.arrow.flight.FlightBindingService$DoGetMethod.invoke(FlightBindingService.java:134)
  at io.grpc.stub.ServerCalls$UnaryServerCallHandler$UnaryServerCallListener.onHalfClose(ServerCalls.java:172)
  at io.grpc.PartialForwardingServerCallListener.onHalfClose(PartialForwardingServerCallListener.java:35)
  at io.grpc.ForwardingServerCallListener.onHalfClose(ForwardingServerCallListener.java:23)

What would you like to be added:
Snowflake Numbers where scale == 0 should be interpreted as integer type. (or long or other non-floating representation).

Why is this needed:
snowflake default integer implementation datatype is 'number(38,0)' reference
In in dremio-snowflake plugin, all numerics are presented as floating representation rather than integer in cases where the scale == 0.
for example, something inserted into snowflake as the integer 5 is extracted and presented in dremio as #.# or 5.0

What happened: I got the error The source [MYSOURCE] is currently unavailable when I hit the RUN button on Dremio UI

What you expected to happen:
Running the query without any errors.

How to reproduce it (as minimally and precisely as possible):
I've created a connection to snowflake and navigate through Dremio UI until I reach one of my Snowflake tables. I could see a sample of the results. Then I clicked on the RUN button on Dremio UI and I got the The source [MYSOURCE] is currently unavailable.

From Raw Profile log: SOURCE_BAD_STATE ERROR: The source [MYSOURCE] is currently unavailable. Info: []

Anything else we need to know?:
On preview mode the query works fine.

Details:

• Dremio version (Help > About from the UI): 19.2.0-202111300654180276-a4ef1150
• Profile zip:
• server.log:

What happened:
Queries fail with IllegalArgumentException: Gandiva only supports decimals of upto 38 precision. Input precision : 0

How to reproduce it (as minimally and precisely as possible):
Build the plugin for 11.0.0 and run a query on a table that has decimals.

Full trace:

SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:7
SqlOperatorImpl JDBC_SUB_SCAN
Location 0:0:7
Fragment 0:0

[Error Id: 0d087a42-b010-4c1e-a859-0204b232a3e4 on localhost:0]

  (java.lang.IllegalArgumentException) Gandiva only supports decimals of upto 38 precision. Input precision : 0
    org.apache.arrow.util.Preconditions.checkArgument():136
    org.apache.arrow.gandiva.expression.ArrowTypeHelper.initArrowTypeDecimal():119
    org.apache.arrow.gandiva.expression.ArrowTypeHelper.arrowTypeToProtobuf():267
    org.apache.arrow.gandiva.expression.FunctionNode.toProtobuf():44
    org.apache.arrow.gandiva.expression.ExpressionTree.toProtobuf():42
    org.apache.arrow.gandiva.evaluator.Projector.make():156
    org.apache.arrow.gandiva.evaluator.Projector.make():95
    com.dremio.sabot.op.llvm.NativeProjector.build():65
    com.dremio.sabot.op.llvm.NativeProjectorBuilder.build():78
    com.dremio.exec.expr.SplitStageExecutor.setupFinish():178
    com.dremio.exec.expr.SplitStageExecutor.setupProjector():214
    com.dremio.exec.expr.ExpressionSplitter.projectorSetup():295
    com.dremio.exec.expr.ExpressionSplitter.setupProjector():396
    com.dremio.exec.store.CoercionReader.setupProjector():166
    com.dremio.exec.store.CoercionReader.newSchema():143
    com.dremio.exec.store.CoercionReader.setup():126
    com.dremio.sabot.op.scan.ScanOperator.setupReaderAsCorrectUser():290
    com.dremio.sabot.op.scan.ScanOperator.setupReader():264
    com.dremio.sabot.op.scan.ScanOperator.setup():249
    com.dremio.sabot.driver.SmartOp$SmartProducer.setup():563
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():79
    com.dremio.sabot.driver.Pipe$SetupVisitor.visitProducer():63
    com.dremio.sabot.driver.SmartOp$SmartProducer.accept():533
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.StraightPipe.setup():102
    com.dremio.sabot.driver.Pipeline.setup():68
    com.dremio.sabot.exec.fragment.FragmentExecutor.setupExecution():391
    com.dremio.sabot.exec.fragment.FragmentExecutor.run():273
    com.dremio.sabot.exec.fragment.FragmentExecutor.access$1400():94
    com.dremio.sabot.exec.fragment.FragmentExecutor$AsyncTaskImpl.run():711
    com.dremio.sabot.task.AsyncTaskWrapper.run():112
    com.dremio.sabot.task.slicing.SlicingThread.mainExecutionLoop():225
    com.dremio.sabot.task.slicing.SlicingThread.run():156```

What happened:

Perform select statement through JDBC Connection (or GUI) and receive VALIDATION ERROR.

SELECT *
FROM "sf-dremio"."PROD_TEMP"."TEMP"."EQUIP" E
INNER JOIN "sf-dremio"."PROD_TEMP"."TEMP"."EQUIPCLUSTER" EC ON E.EKEY = EC.PARENT_EKEY
INNER JOIN "sf-dremio"."PROD_TEMP"."TEMP"."EQUIP" CE ON CE.EKEY = EC.CHILD_EKEY
order by E.VALUE
limit 2

Results in following JDBC error returned:

SQL Error: PARSE ERROR: Failure parsing the query.

SQL Query SELECT *
FROM "sf-dremio"."PROD_TEMP"."TEMP"."EQUIP" E
INNER JOIN "sf-dremio"."PROD_TEMP"."TEMP"."EQUIPCLUSTER" EC ON E.EKEY = EC.PARENT_EKEY
INNER JOIN "sf-dremio"."PROD_TEMP"."TEMP"."EQUIP" CE ON CE.EKEY = EC.CHILD_EKEY
order by E.VALUE
limit 2

startLine 5
startColumn 11
endLine 5
endColumn 11
SQL Query SELECT *
FROM "sf-dremio"."PROD_TEMP"."TEMP"."EQUIP" E
INNER JOIN "sf-dremio"."PROD_TEMP"."TEMP"."EQUIPCLUSTER" EC ON E.EKEY = EC.PARENT_EKEY
INNER JOIN "sf-dremio"."PROD_TEMP"."TEMP"."EQUIP" CE ON CE.EKEY = EC.CHILD_EKEY

What you expected to happen:

Would expect it to not result in an invalid QUERY.

How to reproduce it (as minimally and precisely as possible):

In snowflake,

create or replace table PROD_TEMP.TEMP.Equip (ekey integer, ename varchar(20), value float)
create or replace table PROD_TEMP.TEMP.EquipCluster (ecKey integer AUTOINCREMENT, parent_ekey integer, child_ekey integer, rtype varchar(10), active boolean)

I use dbeaver to create test data using following method.
create 1000 Equip with sequential ekey ename is random 'Ipsum lorum' value is random float.
create series of parent_ekey starting at 1 stepping by 3
create series of child_ekey starting at 2 stepping by 3

Anything else we need to know?:

Details:

• Dremio version (Help > About from the UI):

FULL DISCLOSURE this is OSS Dremio with some extras.

Build
3.2.4-201906051751050278-1bcce62
Edition
Community Edition
Build Time
06/06/2019 22:42:55
Change Hash
1bcce62ffcffe357f061f68993038a98ba58347d
Change Time
06/05/2019 13:44:25

• Profile zip:
• server.log:

2019-08-13 19:53:05,298 [out-of-band-observer] INFO  query.logger - {"queryId":"22aceade-5af1-4f3a-e409-c17b989c5600","queryText":"SELECT *\nFROM \"sf-dremio\".\"PROD_TEMP\".\"TEMP\".\"EQUIP\" E\nINNER JOIN \"sf-dremio\".\"PROD_TEMP\".\"TEMP\".\"EQUIPCLUSTER\" EC ON E.EKEY = EC.PARENT_EKEY\nINNER JOIN \"sf-dremio\".\"PROD_TEMP\".\"TEMP\".\"EQUIP\" CE ON CE.EKEY = EC.CHILD_EKEY\norder by E.VALUE\nlimit 2\n","start":1565725985294,"finish":1565725985296,"outcome":"FAILED","username":"admin"}

I do not know whether this is a bug: the error-msg "Error setting up remote fragment execution" is shown in the context of a simple SELECT *-statement...

What can the reason be for this?

What would you like to be added:

I ran into a stack trace with one of our source tables... it looks like there is no handler for BINARY data type.

2019-08-12 13:13:05,217 [qtp750518820-243] WARN  c.d.e.store.jdbc.dialect.TypeMapper - A column you queried has a data type that is not currently supported by the JDBC storage plugin. The column's name was FOO.BAR.BAZ.KEY, its JDBC data type was BINARY, and the source column type was BINARY.

I tried to follow your example in snowflake-arp.yaml:

    #------------Binary types--------------#
    - source:
        name: "BINARY"
      dremio:
        name: "varbinary"

But not having success yet.

Why is this needed:
In Snowflake, we have columns defined as:

BINARY(20)