# Glitches between Dremio and Vertica

**URL:** https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229
**Category:** Uncategorized
**Created:** [December 1, 2023, 1:44pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229 "2023-12-01T13:44:27Z")
**Posts on this page:** 6
**Page:** 1

<div class="post-metadata">

### Author: ![Anton](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@Anton](https://community.dremio.com/u/Anton)
#### Post date: [December 1, 2023, 1:44pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229/1 "2023-12-01T13:44:27Z")

</div>

Greetings to you all.

We have some problem with Dremio and Vertica.  
For example we use this SQL-query in Dremio (I will write here only part of our query cause it is big enough):

> **Code in Dremio**
>
> with transactions as(
> 
> select
> 
> case /\*when TRX\_DATE between TO\_TIMESTAMP(‘2009-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2010-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2010
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2010-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2011-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2011
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2011-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2012-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2012
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2012-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2013-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2013
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2013-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2014-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2014
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2014-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2015-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2015\*/
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2015-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2016-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2016
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2016-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2017-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2017
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2017-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2018-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2018
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2018-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2019-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2019
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2019-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2020-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2020
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2020-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2021-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2021
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2021-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2022-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2022
> 
> when TRX\_DATE between TO\_TIMESTAMP(‘2022-10-01 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) and TO\_TIMESTAMP(‘2023-09-30 00:00:00.000’, ‘YYYY-MM-DD HH24:MI:SS.FFF’) then 2023
> 
> end flight\_period,
> 
> cast (FFP\_NO as bigint) ffp\_no,
> 
> TRANSACTION\_ID,
> 
> min (TRX\_DATE) TRX\_DATE

And on the Vertica side we have this query transformed like this:

> **What we have in vertica**
>
> SELECT “ffp\_transactions\_V”.“FFP\_NO”,  
> “ffp\_transactions\_V”.“TRX\_DATE”,  
> “ffp\_transactions\_V”.“ACCRUAL\_REDEMPTION\_STATUS”,  
> “ffp\_transactions\_V”.“TRX\_STATUS”,  
> “ffp\_transactions\_V”.“TEST\_ACCOUNT\_FLAG”,  
> “ffp\_transactions\_V”.“TRX\_TRANSFER\_FLAG”,  
> “ffp\_transactions\_V”.“TRANSACTION\_DESC”,  
> “ffp\_transactions\_V”.“BUSINESS\_LINE”  
> FROM “edw2”.“dm”.“ffp\_transactions\_V”

As I see the Dremio just wants to get all the data (without using any of the specified ranges).  
Is there any possibility to tune the Vertica connector using file in this repo [https://github.com/luciodaza/dremio-vertica-connector/blob/master/src/main/resources/arp/implementation/vertica-arp.yaml](https://github.com/luciodaza/dremio-vertica-connector/blob/master/src/main/resources/arp/implementation/vertica-arp.yaml) and build our own version with needed functionality?

---

<div class="post-metadata">

### Author: ![lenoyjacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lenoyjacob/32/3474_2.png) [@lenoyjacob](https://community.dremio.com/u/lenoyjacob)
#### Post date: [December 1, 2023, 5:11pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229/2 "2023-12-01T17:11:53Z")

</div>

> [@Anton](#):
>
> Is there any possibility to tune the Vertica connector using file in this repo [https://github.com/luciodaza/dremio-vertica-connector/blob/master/src/main/resources/arp/implementation/vertica-arp.yaml](https://github.com/luciodaza/dremio-vertica-connector/blob/master/src/main/resources/arp/implementation/vertica-arp.yaml) and build our own version with needed functionality?

Of course, these are community built ARP connectors. Feel free to fork and fix any issues (check out other forks as well). Here’s a quick tutorial on what’s important in an ARP connector → [How to Create an ARP Connector | Dremio](https://www.dremio.com/resources/tutorials/how-to-create-an-arp-connector/)

Also, a quick workaround would be to use External Queries. See [here](https://docs.dremio.com/current/reference/sql/commands/SELECT-statements/#querying-relational-database-sources-with-external-queries).

```auto
SELECT * FROM table(<vertica_datasource-name>.external_query('<vertica-query'))

```

---

<div class="post-metadata">

### Author: ![Anton](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@Anton](https://community.dremio.com/u/Anton)
#### Post date: [December 4, 2023, 3:09pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229/3 "2023-12-04T15:09:55Z")

</div>

> [@lenoyjacob](#):
>
> External Queries

Thanks for the info.

I have tried this query, but it fails:

> select \* FROM table(EDW2.edw2.dm.ffp\_transactions\_V.external\_query(‘where TRX\_DATE between ‘‘2015-10-01’’ and ‘‘2023-09-30’’’))

The error is:

> No match found for function signature external\_query().

I think this is due to the wrong syntax, but I’m using the double simple quotes here (there is no double quotes).

Am I missing something?

---

<div class="post-metadata">

### Author: ![lenoyjacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lenoyjacob/32/3474_2.png) [@lenoyjacob](https://community.dremio.com/u/lenoyjacob)
#### Post date: [December 4, 2023, 4:14pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229/4 "2023-12-04T16:14:58Z")

</div>

> [@Anton](#):
>
> I think this is due to the wrong syntax, but I’m using the double simple quotes here (there is no double quotes).

1. Is `EDW2.edw2.dm.ffp_transactions_V` your Source name? You can generally find the source name in the bottom left side. For example, here are mine:  
 ![image](https://us1.discourse-cdn.com/flex020/uploads/dremio/original/2X/a/a1ed0ee3aa511f710d744b06d90007e460922efe.png)

2. You need to put the **entire** Vertica query (as how you would run it in Vertica) between quotes. So for example, if I want to query some table from the source “pg” from the above image. I run the following query:

---

<div class="post-metadata">

### Author: ![Anton](https://avatars.discourse-cdn.com/v4/letter/a/d9b06d/32.png) [@Anton](https://community.dremio.com/u/Anton)
#### Post date: [December 5, 2023, 2:35pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229/5 "2023-12-05T14:35:42Z")

</div>

Thanks for your help!  
The variant with the use of **external\_query** works now.  
You were right - it has been the wrong syntax.

Also I want to know about configuring the Vertica connector through vertica-arp.yaml ([https://github.com/luciodaza/dremio-vertica-connector/blob/master/src/main/resources/arp/implementation/vertica-arp.yaml](https://github.com/luciodaza/dremio-vertica-connector/blob/master/src/main/resources/arp/implementation/vertica-arp.yaml)).  
I’m not good in Java and coding.  
Maybe there is some manual about how to tune and build the connector through this vertica-arp.yaml file?

---

<div class="post-metadata">

### Author: ![lenoyjacob](https://sea2.discourse-cdn.com/flex020/user_avatar/community.dremio.com/lenoyjacob/32/3474_2.png) [@lenoyjacob](https://community.dremio.com/u/lenoyjacob)
#### Post date: [December 5, 2023, 4:46pm UTC](https://community.dremio.com/t/glitches-between-dremio-and-vertica/11229/6 "2023-12-05T16:46:28Z")

</div>

> [@Anton](#):
>
> The variant with the use of **external\_query** works now.

Great to hear that it works!

> [@Anton](#):
>
> Maybe there is some manual about how to tune and build the connector through this vertica-arp.yaml file?

Here’s the guide that may help you: [Dremio Tutorial: Creating ARP Connector](https://www.dremio.com/resources/tutorials/how-to-create-an-arp-connector/) IMO, building an ARP connector isn’t too difficult. Try it out and let us know if you need any help.