You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@drill.apache.org by "Spinn, Brandi" <Br...@siriusxm.com> on 2017/12/15 21:45:53 UTC
Drill Push to Tableau, Error -
Hello,
We are currently running a project which is utilizing the Drill push to Tableau function to be able to work with our data sets, we are already working with Tableau regarding our needs and determined that this is our best course of action considering how large data sets are - over 2 million rows per day.
At the moment we have several visualizations we have published, but we are running in an issue each morning where some of them are not updating according to our schedules, and when we review the logs we find some the same type of "fatal" errors that do not always allow the visualizations to update.
I have reached out to our account rep through Tableau for possible guidance, however, you might be a more appropriate resource; any help you could provide would be appreciated, I have not been able to find much via the internets.
Below is a sample of the error codes we are seeing, please let me know you are able to assist or if you need any additional information. Thank you!
Auto_AOD_Content:288 - create table dfs.tmp.attributes as select distinct channels.marketingname as channelmarketingname, channels.streamingname as channelstreamingname, channels.channelguid as channelGuid, channels.channelid as channelId, categories.category_name as channelcategory, CASE when music.channel_guid is not null then 'Music' else null end as genre_Music, CASE when news.channel_guid is not null then 'News' else null end as genre_News, CASE when sports.channel_guid is not null then 'Sports' else null end as genre_Sports, CASE when talk.channel_guid is not null then 'Talk' else null end as genre_Talk, CASE when howard.channel_guid is not null then 'Howard' else null end as genre_Howard, categories.channel_name channelName from dfs.root.`/SXM/archive/parsed/Channel-parsed-type2/2017-12-14*` channels join dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` categories on categories.channel_guid=channels.channelguid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Music') music on channels.channelguid = music.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'News') news on channels.channelguid = news.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Sports') sports on channels.channelguid = sports.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Talk') talk on channels.channelguid = talk.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Howard') howard on channels.channelguid = howard.channel_guid
Auto_AOD_Content:304 - There was a SQL ERROR on DRILL side
Auto_AOD_Content:305 - VALIDATION ERROR: A table or view with given name [attributes] already exists in schema [dfs.tmp]
[cid:image001.png@01CEDACC.AD7124E0]
Data Analyst, Streaming Analytics
8650 Freeport Parkway
Irving, TX 75063
p: #817-845-3597
e: Brandi.Spinn@siriusxm.com<ma...@siriusxm.com>
Re: Drill Push to Tableau, Error -
Posted by Andries Engelbrecht <ae...@mapr.com>.
Does Tableau automatically generate the DML or is it user generated?
Is the table used for multiple sessions or only for a single session?
Does other session create the same/similar tables for use?
As Kunal mentioned temporary tables may the way to go.
--Andries
On 12/17/17, 9:36 AM, "Kunal Khatua" <kk...@mapr.com> wrote:
It looks like your system is trying to create the attributes table concurrently. Based on the name of the table, my hunch is that your workflow(s) is/are trying to generate tables with the same name (attributes) for use in reporting.
The suggested way to work around this is to use a naming convention that hints to the report(s) that will leverage the tables.
So, instead of 2 or more reports trying to create the same 'attributes' table, how about you try something like .. 'attr_report1' .. 'attr_report2' , etc. ?
Also, if these tables are transient in nature, you can consider using the 'create temp table as ...' SQL. This will create temporary tables with the lifespan of the connection you're working on. The moment you close that connection, Drill will clean up. So, if you have multiple connections trying to create a temporary 'attributes' table, they will all be isolated.
Hope that helps. Let us know how else you are using Drill.
Thanks
~ Kunal
-----Original Message-----
From: Kunal Khatua [mailto:kkhatua@mapr.com]
Sent: Sunday, December 17, 2017 9:30 AM
To: user@drill.apache.org
Subject: FW: Drill Push to Tableau, Error -
Forwarded from dev@drill.apache.org
From: Spinn, Brandi [mailto:Brandi.Spinn@siriusxm.com]
Sent: Friday, December 15, 2017 1:46 PM
To: dev@drill.apache.org
Subject: Drill Push to Tableau, Error -
Hello,
We are currently running a project which is utilizing the Drill push to Tableau function to be able to work with our data sets, we are already working with Tableau regarding our needs and determined that this is our best course of action considering how large data sets are - over 2 million rows per day.
At the moment we have several visualizations we have published, but we are running in an issue each morning where some of them are not updating according to our schedules, and when we review the logs we find some the same type of "fatal" errors that do not always allow the visualizations to update.
I have reached out to our account rep through Tableau for possible guidance, however, you might be a more appropriate resource; any help you could provide would be appreciated, I have not been able to find much via the internets.
Below is a sample of the error codes we are seeing, please let me know you are able to assist or if you need any additional information. Thank you!
Auto_AOD_Content:288 - create table dfs.tmp.attributes as select distinct channels.marketingname as channelmarketingname, channels.streamingname as channelstreamingname, channels.channelguid as channelGuid, channels.channelid as channelId, categories.category_name as channelcategory, CASE when music.channel_guid is not null then 'Music' else null end as genre_Music, CASE when news.channel_guid is not null then 'News' else null end as genre_News, CASE when sports.channel_guid is not null then 'Sports' else null end as genre_Sports, CASE when talk.channel_guid is not null then 'Talk' else null end as genre_Talk, CASE when howard.channel_guid is not null then 'Howard' else null end as genre_Howard, categories.channel_name channelName from dfs.root.`/SXM/archive/parsed/Channel-parsed-type2/2017-12-14*` channels join dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` categories on categories.channel_guid=channels.channelguid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Music') music on channels.channelguid = music.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'News') news on channels.channelguid = news.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Sports') sports on channels.channelguid = sports.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Talk') talk on channels.channelguid = talk.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Howard') howard on channels.channelguid = howard.channel_guid
Auto_AOD_Content:304 - There was a SQL ERROR on DRILL side
Auto_AOD_Content:305 - VALIDATION ERROR: A table or view with given name [attributes] already exists in schema [dfs.tmp]
[cid:image001.png@01CEDACC.AD7124E0]
Data Analyst, Streaming Analytics
8650 Freeport Parkway
Irving, TX 75063
p: #817-845-3597
e: Brandi.Spinn@siriusxm.com<ma...@siriusxm.com>
RE: Drill Push to Tableau, Error -
Posted by Kunal Khatua <kk...@mapr.com>.
It looks like your system is trying to create the attributes table concurrently. Based on the name of the table, my hunch is that your workflow(s) is/are trying to generate tables with the same name (attributes) for use in reporting.
The suggested way to work around this is to use a naming convention that hints to the report(s) that will leverage the tables.
So, instead of 2 or more reports trying to create the same 'attributes' table, how about you try something like .. 'attr_report1' .. 'attr_report2' , etc. ?
Also, if these tables are transient in nature, you can consider using the 'create temp table as ...' SQL. This will create temporary tables with the lifespan of the connection you're working on. The moment you close that connection, Drill will clean up. So, if you have multiple connections trying to create a temporary 'attributes' table, they will all be isolated.
Hope that helps. Let us know how else you are using Drill.
Thanks
~ Kunal
-----Original Message-----
From: Kunal Khatua [mailto:kkhatua@mapr.com]
Sent: Sunday, December 17, 2017 9:30 AM
To: user@drill.apache.org
Subject: FW: Drill Push to Tableau, Error -
Forwarded from dev@drill.apache.org
From: Spinn, Brandi [mailto:Brandi.Spinn@siriusxm.com]
Sent: Friday, December 15, 2017 1:46 PM
To: dev@drill.apache.org
Subject: Drill Push to Tableau, Error -
Hello,
We are currently running a project which is utilizing the Drill push to Tableau function to be able to work with our data sets, we are already working with Tableau regarding our needs and determined that this is our best course of action considering how large data sets are - over 2 million rows per day.
At the moment we have several visualizations we have published, but we are running in an issue each morning where some of them are not updating according to our schedules, and when we review the logs we find some the same type of "fatal" errors that do not always allow the visualizations to update.
I have reached out to our account rep through Tableau for possible guidance, however, you might be a more appropriate resource; any help you could provide would be appreciated, I have not been able to find much via the internets.
Below is a sample of the error codes we are seeing, please let me know you are able to assist or if you need any additional information. Thank you!
Auto_AOD_Content:288 - create table dfs.tmp.attributes as select distinct channels.marketingname as channelmarketingname, channels.streamingname as channelstreamingname, channels.channelguid as channelGuid, channels.channelid as channelId, categories.category_name as channelcategory, CASE when music.channel_guid is not null then 'Music' else null end as genre_Music, CASE when news.channel_guid is not null then 'News' else null end as genre_News, CASE when sports.channel_guid is not null then 'Sports' else null end as genre_Sports, CASE when talk.channel_guid is not null then 'Talk' else null end as genre_Talk, CASE when howard.channel_guid is not null then 'Howard' else null end as genre_Howard, categories.channel_name channelName from dfs.root.`/SXM/archive/parsed/Channel-parsed-type2/2017-12-14*` channels join dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` categories on categories.channel_guid=channels.channelguid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Music') music on channels.channelguid = music.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'News') news on channels.channelguid = news.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Sports') sports on channels.channelguid = sports.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Talk') talk on channels.channelguid = talk.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Howard') howard on channels.channelguid = howard.channel_guid
Auto_AOD_Content:304 - There was a SQL ERROR on DRILL side
Auto_AOD_Content:305 - VALIDATION ERROR: A table or view with given name [attributes] already exists in schema [dfs.tmp]
[cid:image001.png@01CEDACC.AD7124E0]
Data Analyst, Streaming Analytics
8650 Freeport Parkway
Irving, TX 75063
p: #817-845-3597
e: Brandi.Spinn@siriusxm.com<ma...@siriusxm.com>
FW: Drill Push to Tableau, Error -
Posted by Kunal Khatua <kk...@mapr.com>.
Forwarded from dev@drill.apache.org
From: Spinn, Brandi [mailto:Brandi.Spinn@siriusxm.com]
Sent: Friday, December 15, 2017 1:46 PM
To: dev@drill.apache.org
Subject: Drill Push to Tableau, Error -
Hello,
We are currently running a project which is utilizing the Drill push to Tableau function to be able to work with our data sets, we are already working with Tableau regarding our needs and determined that this is our best course of action considering how large data sets are - over 2 million rows per day.
At the moment we have several visualizations we have published, but we are running in an issue each morning where some of them are not updating according to our schedules, and when we review the logs we find some the same type of "fatal" errors that do not always allow the visualizations to update.
I have reached out to our account rep through Tableau for possible guidance, however, you might be a more appropriate resource; any help you could provide would be appreciated, I have not been able to find much via the internets.
Below is a sample of the error codes we are seeing, please let me know you are able to assist or if you need any additional information. Thank you!
Auto_AOD_Content:288 - create table dfs.tmp.attributes as select distinct channels.marketingname as channelmarketingname, channels.streamingname as channelstreamingname, channels.channelguid as channelGuid, channels.channelid as channelId, categories.category_name as channelcategory, CASE when music.channel_guid is not null then 'Music' else null end as genre_Music, CASE when news.channel_guid is not null then 'News' else null end as genre_News, CASE when sports.channel_guid is not null then 'Sports' else null end as genre_Sports, CASE when talk.channel_guid is not null then 'Talk' else null end as genre_Talk, CASE when howard.channel_guid is not null then 'Howard' else null end as genre_Howard, categories.channel_name channelName from dfs.root.`/SXM/archive/parsed/Channel-parsed-type2/2017-12-14*` channels join dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` categories on categories.channel_guid=channels.channelguid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Music') music on channels.channelguid = music.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'News') news on channels.channelguid = news.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Sports') sports on channels.channelguid = sports.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Talk') talk on channels.channelguid = talk.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Howard') howard on channels.channelguid = howard.channel_guid
Auto_AOD_Content:304 - There was a SQL ERROR on DRILL side
Auto_AOD_Content:305 - VALIDATION ERROR: A table or view with given name [attributes] already exists in schema [dfs.tmp]
[cid:image001.png@01CEDACC.AD7124E0]
Data Analyst, Streaming Analytics
8650 Freeport Parkway
Irving, TX 75063
p: #817-845-3597
e: Brandi.Spinn@siriusxm.com<ma...@siriusxm.com>
RE: Drill Push to Tableau, Error -
Posted by Kunal Khatua <kk...@mapr.com>.
First, your problem appears to be user based, so, mailing on the User List is more appropriate. I'll forward this there and attempt to help you resolve your issue.
This mailing list is more focused on developers of Drill or people who might have interest in contributing to Drill's development (by code, evangelists, etc).
From: Spinn, Brandi [mailto:Brandi.Spinn@siriusxm.com]
Sent: Friday, December 15, 2017 1:46 PM
To: dev@drill.apache.org
Subject: Drill Push to Tableau, Error -
Hello,
We are currently running a project which is utilizing the Drill push to Tableau function to be able to work with our data sets, we are already working with Tableau regarding our needs and determined that this is our best course of action considering how large data sets are - over 2 million rows per day.
At the moment we have several visualizations we have published, but we are running in an issue each morning where some of them are not updating according to our schedules, and when we review the logs we find some the same type of "fatal" errors that do not always allow the visualizations to update.
I have reached out to our account rep through Tableau for possible guidance, however, you might be a more appropriate resource; any help you could provide would be appreciated, I have not been able to find much via the internets.
Below is a sample of the error codes we are seeing, please let me know you are able to assist or if you need any additional information. Thank you!
Auto_AOD_Content:288 - create table dfs.tmp.attributes as select distinct channels.marketingname as channelmarketingname, channels.streamingname as channelstreamingname, channels.channelguid as channelGuid, channels.channelid as channelId, categories.category_name as channelcategory, CASE when music.channel_guid is not null then 'Music' else null end as genre_Music, CASE when news.channel_guid is not null then 'News' else null end as genre_News, CASE when sports.channel_guid is not null then 'Sports' else null end as genre_Sports, CASE when talk.channel_guid is not null then 'Talk' else null end as genre_Talk, CASE when howard.channel_guid is not null then 'Howard' else null end as genre_Howard, categories.channel_name channelName from dfs.root.`/SXM/archive/parsed/Channel-parsed-type2/2017-12-14*` channels join dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` categories on categories.channel_guid=channels.channelguid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Music') music on channels.channelguid = music.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'News') news on channels.channelguid = news.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Sports') sports on channels.channelguid = sports.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Talk') talk on channels.channelguid = talk.channel_guid left join (select channel_guid from dfs.root.`/SXM/archive/parsed/category-parsed-type2/2017-12-14*` where supercategory_name = 'Howard') howard on channels.channelguid = howard.channel_guid
Auto_AOD_Content:304 - There was a SQL ERROR on DRILL side
Auto_AOD_Content:305 - VALIDATION ERROR: A table or view with given name [attributes] already exists in schema [dfs.tmp]
[cid:image001.png@01CEDACC.AD7124E0]
Data Analyst, Streaming Analytics
8650 Freeport Parkway
Irving, TX 75063
p: #817-845-3597
e: Brandi.Spinn@siriusxm.com<ma...@siriusxm.com>