Questions tagged with Amazon Athena
Content language: English
Sort by most recent
We are tying to call the AWS Athena from Tableau on local desktop and it works fine. We have changed the firewall settings and open the port 443,444.
Now, we tried to do the same thing from Tableau server (like published the Data source and deployed the Tableau Dashboard) but we are constantly getting the error like below -
**"[Simba][AthenaJDBC](100071) An error has been thrown from the AWS Athena client. [ErrorCategory:USER_ERROR, ErrorCode:PERMISSION_ERROR], Detail:Amazon Athena experienced a permission error. Please provide proper permission and submitting the query again. If the issue reoccurs, contact AWS support for further assistance. You will not be charged for this query. We apologize for the inconvenience., Message:Amazon Athena experienced a permission error. Please provide proper permission and submitting the query again. If the issue reoccurs, contact AWS support for further assistance. You will not be charged for this query. We apologize for the inconvenience. [Execution ID: d2fd7dfc-f955-4294-bfa8-b9ebef1b5ad0]"**
Tableau - 2021.4.3 version
TableauAthena Driver - AthenaJDBC42-2.0.33 version
Can anybody help me to solve this issue.
Thanks in advance.
So we noticed an issue after upgrading from the Athena v2 engine to v3 it happens when trying to use CTAS to create a table and bucket the data based on a timestamp this is definitely a new issue because we have been using the same code with no issue in v2. prior to upgrading we went throught the breaking changes and did not see any changes to the supported bucket types, or any other issues that would cause this issues to occur i am curious if anyone has encountered this issue and if they where able to resolve the problem
Test query: this was a extremely cut down version of our initial query that we changed to isolate the issue we used the localtimestamp to eliminate any potential issues on our side and the error would remain the same
```
create
table
tmp.test_bucket1 with
(
bucketed_by = ARRAY['timestamp']
, bucket_count = 1
, external_location = '[S3_DATA]'
)
as (
select
localtimestamp as timestamp
)
```
Error: 
from what we feel like it could be something related to the timestamp breaking changes in the new Athena engine that have been made that might be having unexpected consequences on the bucketing process if anyone would be able to provide some more guidance or ideas it would be much appreciated for the time being we have reverted to V2 where timestamp bucketing is working until we can track down the cause for this
I'm looking at the utilization of my S3 buckets in an account, and would like to know if there is a good way to look at the date of last interaction across all of them.
The intention is to find these under-utilized or non-utilized buckets and make some decisions around what to do with the data. I'm already using intelligent tiering with these which does help somewhat, this would be a manual evaluation process though.
When creating a view (in a Glue Catalog Database) which uses geometric functions using Athena engine version 3, I get the error `Unknown type: geometry`. Using "Athena engine version 2" I don't get an error.
Example query:
```sql
CREATE VIEW test_view1 AS
SELECT * FROM (Values (1)) as t(id)
WHERE ST_CONTAINS(ST_GeometryFromText('POLYGON((0 2,1 1,0 -1,0 2))'), ST_Point(0.9, 1))
```
Note: This is a simplified query to be able to quickly reproduce the error. The error message is also shown when using an existent table instead of `(Values (1)) as t(id)` or when reading the values ('POLYGON((0 2,1 1,0 -1,0 2))', 0.9, 1) from other tables or when using other functions but ST_CONTAINS.
When running:
```sql
SELECT ST_CONTAINS(ST_GeometryFromText('POLYGON((0 2,1 1,0 -1,0 2))'), ST_Point(0.9, 1))
```
The result is "true" as expected.
Creating views without geometric functions also works.
Also when creating the view directly locally in Trino version 398 (using `docker run --name trino -d -p 8080:8080 trinodb/trino:398`) in the predefined memory catalog, this error is not displayed.
Can those geometric functions somehow be used when creating views using Athena 3 in a Glue Catalog Database?
Facing the below issue while using Athena to load the Partitioned details.
```
HIVE_UNKNOWN_ERROR: serDe should not be accessed from a null StorageFormat
This query ran against the "mydb" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 25a2a209-4826-4861-909e-059fe954e12b
```
I am planning to utilize catalogPartitionPredicate in one of my projects. I am unable to handle one of the scenarios. Below are the details:
1. Partition columns: Year,Month & Day
2. catalogPartitionPredicate: year>='2021' and month>='12'
If the year changes to 2022(2022-01-01) and I want to read data from 2021-12-01; the expression won't be able to handle as it will not allow to read 2022 data. I tried to concat the partition keys but it didn't work.
Is there any way to implement to_date functionality or any other workaround to handle this scenario?
I am trying to point my Athena table to read our tflmedia-new Cloudwatch access logs, so that I can find who is still accessing our bucket objects using TLSv1/1.1. I understand those TLS versions will not be allowed by AWS very soon, so we need to determine which clients are still using the older TLS versions.
However, those Cloudwatch access logs are .gz files, so Athena evidently can't read them?
Here is the query I used to create the Athena table:
CREATE EXTERNAL TABLE `s3_access_logs_db.cloudtrail_logs`(
`bucketowner` STRING,
`bucket_name` STRING,
`requestdatetime` STRING,
`remoteip` STRING,
`requester` STRING,
`requestid` STRING,
`operation` STRING,
`key` STRING,
`request_uri` STRING,
`httpstatus` STRING,
`errorcode` STRING,
`bytessent` BIGINT,
`objectsize` BIGINT,
`totaltime` STRING,
`turnaroundtime` STRING,
`referrer` STRING,
`useragent` STRING,
`versionid` STRING,
`hostid` STRING,
`sigv` STRING,
`ciphersuite` STRING,
`authtype` STRING,
`endpoint` STRING,
`tlsversion` STRING,
`accesspointarn` STRING,
`aclrequired` STRING)
ROW FORMAT SERDE
'org.apache.hadoop.hive.serde2.RegexSerDe'
WITH SERDEPROPERTIES (
'input.regex'='([^ ]*) ([^ ]*) \\[(.*?)\\] ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) (-|[0-9]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) (\"[^\"]*\"|-) ([^ ]*)(?: ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*) ([^ ]*))?.*$')
STORED AS INPUTFORMAT
'org.apache.hadoop.mapred.TextInputFormat'
OUTPUTFORMAT
'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
's3://tflmedia-new/cloudtrail/AWSLogs/837411345956/CloudTrail/us-east-1/2023/02/13/'
Any help here would be greatly appreciated!
Hi all,
I've found an old query which I'm trying to understand. I've a question about the FROM part. I understand that the temporary table is a key-valuepair. But I don't understand what happens in the from part. Because it returns the dutch names of the week (created in the key-valuepair), but why? The query looks like this:
WITH
days AS (
SELECT "map"(ARRAY['Sunday','Monday','Tuesday','Wednesday','Thursday','Friday','Saturday'],
ARRAY['Zondag','Maandag','Dinsdag','Woensdag','Donderdag','Vrijdag','Zaterdag']) "dagen"
)
SELECT
, "date"("tabel1"."datumtijd") "date"
, "dagen"["date_format"("date"("tabel1"), '%W')] "days"
FROM
days
, "tabel1"
LEFT JOIN "tabel2 "ON (....
Trying to DROP TABLE in Athena and getting the following error:
[ErrorCategory:USER_ERROR, ErrorCode:PERMISSION_ERROR], Detail:Amazon Athena experienced a permission error. Please provide proper permission and submitting the query again. If the issue reoccurs, contact AWS support for further assistance.
I also tried deleting from Glue under tables and got this error:
Failed to delete all tables.
AccessDeniedException: Insufficient Lake Formation permission(s): Required Drop on [table name]
I went to Lake Formation and granted myself SUPER on the database I'm trying to delete from but I'm still getting the same errors.
asked a month ago
schema name was not found when running a query lambda function into Athena. However, when running the same exact query in Athena it works (see attachment)
When I am trying to make a call from Athena Client (call from react js), it is timed out with this error at Athena....
This query ran against the "xxx_analytics_database" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 85994bac-a81c-42ed-92de-6685dfc90172
HIVE_METASTORE_ERROR: Error: name expected at the position 13527 of 'struct<Activity:string,DisplayName:string,State:string,Variables:struct<triggersDataFilePath:string,dt_triggersData:string,boo_ToContinueProcess:string,dic_triggersFiles:string,triggersDataFilePathCurrent:string,boo_excelReadSuccess:string,lastExchangeCheck:string,str_triggersFilesHistoryFileContent:string,boo_ToUnraiseUsageFlag:string,boo_asset_InUse:string,boo_ShouldStop:string,API_Username:string,currentTriggerPriority:string,str_CurrentQueueItemReference:string,ProcessSetup:string,DefaultExchangeService:string,currentTriggerRowIndex:string,currentTriggerName:string,currentTriggerMailServer:string,currentTriggerUserDomain:string,currentTriggerUserName:string,currentTriggerSharedMailBox:string,currentTriggerMailFolder:string,currentTriggerSubjectFilterContains:string,currentTriggerBodyFilterContains:string,currentTriggerFrom:string,currentTriggerTo:string,currentTriggerAttachmentsType:string,currentTriggerQueueName:string,currentTriggerMailsFound:string,currentTriggerOrchestratorFolderPath:string,currentTriggerMailServerVersion:string,FilterExpression:string,ExhangeScopeException:string,GeneralOrchFolder:string,SystemException:string,BusinessException:string,TransactionNumber:string,Config:string,RetryNumber:string,TransactionField2:string,TransactionField1:string,TransactionID:string,TransactionsTable:string,TransactionResults:string,ThisRoboticProcessDescription:string,TransactionItem:string,ThisRoboticProcessName:string,FileServer:string,RunningEnvironment:string,in_ConfigSheets:string,ConfigString:string,AssetsString:string,AssetsJson:string,RootOrchFolder:string,GeneralOrchFolderPattern:string,RootOrchFolderPattern:string,Assets:string,item:string,AssetValue:string,testDic:string,jsonString:string,jsonObj:string,ShouldStop:string,boo_shouldStop:string,tran_currentTranItem:string,list_transactionSystemExceptions:string,list_transactionBusinessExceptions:string,str_takenScreenshotFullPath:string,str_takenScreenshotNameOnly:string,int_totalTransactionsWithSystemExceptionInA_Row:string,int_handledTransactionsCounter:string,boo_IsNowProcessDownTime:string,boo_IsNowBusinessAppsDownTime:string,dic_str_obj_AllTransactionData:string,timespan_NextOK_Time:string,boo_SendTransactionToActionCenter:string,boo_TransactionPostponed:string,boo_StopJobBecauseOfUrgent:string,boo_InGlobalError:string,obj_temp:string,App:string,RequiresUserInteraction:string,ConfigFilePath:string,BusinessProcessFolderPath:string,SettingTableFileName:string,SettingTableFileShortPath:string,SettingTableFileFullPath:string,SettingsTables_SheetsKeysAndNames:string,SettingsTables_FilePath:string,SettingsTables:string,TempFilePath:string,SheetKeyAndName:string,SheetAsDataTable:string,JobTempFolderPath:string,CleanMainTempFolder:string,MainTempFolder:string,int_attemptsToOpenBusinessApp:string,InvolvedApps:string,boo_TransactionSuccess:string,str_CurrentTranTempFolderPath:string,CurrentTranMail:string,MailUID:string,ShardMailbox:string,MailFolderToLookup:string,CurrentTranTempFolderPath:string,dt_MailSettings:string,variable2:string,TestDriveFolder:string,TemplateFolder:string,InputFolder:string,DateStamp:string,DateSetup:string,ExcelValidationTemplate:string,strCurrency:string,RecipientEmail:string,EmailBody:string,EmailSubject:string,WebPageURL:string,ScreenShotFolder:string,BOISSName:string,strOtherCurrency:string,exDateSetup:string,strFXCurrency:string,WorkflowName:string,CurrencyFilesPath:string,AttachList:string,MissingFileInFolder:string,File:string,Excelpath:string,FilesNewPath:string,CurrencyRate:string,CurrencyRatio:string,CurrencyRateCalc:string,DTrow:string,TextFileDT:string,ColumnE:string,TextFile:string,Index:string,ExtractDataTable:string,Vendorcode:string,SendEmail:string,CopyEmail:string,MonthsToReduce:string,ReducedMonth:string,ReducedYear:string,LFA1DT:string,ADR6DT:string,AddressNumber:string,EmailTo:string,TelephoneNumber:string,EMailSubject:string,ADR6FilteredDt:string,PageLoaded:string,dt_ExceptionsSettings:string,dt_MailSetting:string,NewExceptionObj:string,ExceptionType:string,ExceptionMessage:string,ExceptionActivityDisplayName:string,json_ExceptionData:string,ExceptionActivityFullName:string,ExceptionWorkflowFile:string,row:string,row_ExceptionTypeLike:string,row_MessageLike:string,row_ActivityDisplayNameLike:string,row_ActivityFullNameLike:string,row_WorkflowFileLike:string,row_ConvertToBusiness:string,row_NewMessage:string,row_MailSettingKey:string,row_DisableRule:string,TruePattern:string,row_ProjectNameLike:string,NewExceptionMessage:string,RemedyConnection:string,StorageFileList:string,DuplicatesViewNames:string,SalesforceEntityName:string,ListViewName:string,dt_ListView:string,TriggerHistory_StorageFilePath:string,TriggerHistory_IdsList:string,QueueName:string,Priority:string,BusinessProcessName:string,BusinessDepartmentName:string,TriggerName:string,QueueOrchFolder:string,dRows_NewQueueItems:string,Exceptions_IDs:string,NewTriggerHistory_IdsList:string,SFResponse:string,TriggerHistory_StorageFileText:string,Reference:string,SpecificContent:string,CreatedDate:string,SAP_System_Name:string,SAP_System_Prefix:string,InputFolderPath:string,DaysToReduce:string,OutputFolderPath:string,CSVFilePath:string,DateAndTimeStamp:string,CSVFileName:string,BusinessEmail:string,MasterFolder:string,ErrorFileName:string,DT_Input:string,ScoraInputFile:string,ScoraOutputFile:string,ScoraErrorFolder:string,ScoraErrorFile:string,ScoraOutputFolder:string,ScoraSuccessFolder:string,ScoraOldFolder:string,ScoraInputFolder:string,ScoraOutputCSVFile:string,DateAndTime:string,Material:string,BillTo:string,ShipTo:string,SourceDT:string,ExceptionDate:string,MetValidSubject:string,SapTransactionCode:string,SAP_Element:string,Message_Type:string,Message_Text:string,Message_Data:string,VariantFilter:string,andArgumentResolver:string,JobGUID:string,EnvironmentFilesDirectoryPath:string,in_boo_endlessProcess:string,in_str_OrchestratorMainFolderName:string,in_str_GeneralFolderName:string,in_str_CustomMainRPA_FolderPath:string,in_int_MinutesDelayBetweenMailsSeeking:string,in_str_BusinessProcessGeneralFolderName:string,dt_NewItems:string,dt_FailedItems:string,TenantName:string,ADUserName:string,MachineName:string,GeneralOrchFolderPath:string,TempFolderPath:string,LogDirectoryFiles:string,ObjectKeyName:string,UploadedObjectDetails:string,FileDate:string,FileName:string,CopyFilePath:string,FileContent:string,AWS_AccessKey_ID:string,AWS_Secret_AccessKey:string,Bucket:string,BucketDetails:string,DList_TriggersData:string,SubTriggersList:string>,Arguments:struct<in_RunParallel:string,in_int_MinutesDelayBetweenMailsSeeking:string,in_str_OrchestratorMainFolderName:string,in_str_BusinessProcessGeneralFolderName:string,in_str_CustomMainRPA_FolderPath:string,in_boo_endlessProcess:string,in_str_GeneralFolderName:string,in_TriggersDataFileName:string,EventManager:string,Value:string,To:string,JsonString:string,JsonObject:string,BusinessDepartment:string,BusinessProcess:string,ContinueOnError:string,RetryInterval:string,NumberOfRetries:string,Message:string,Level:string,Condition:string,out_RootOrchestratorFolder:string,out_GeneralFolderName:string,io_ProcessSetup:string,in_arr_arr_str_ProjectDownTimes_Specific:string,in_arr_arr_str_ProjectDownTimes_Friday:string,in_arr_arr_str_ProjectDownTimes_Weekday:string,in_arr_str_InvolvedApps:string,in_OrcehstartorMainFolderName:string,in_str_BusinessProcessName:string,in_str_BusinessDepartmentName:string,in_arr_arr_str_ProjectDownTimes_Saturday:string,TimeoutMS:string,AssetName:string,FolderPath:string,Username:string,out_ExchangeService:string,Result:string,in_sheetName:string,triggersDataFilePath:string,LogExit:string,ArgumentsVariable:string,Timeout:string,LogEntry:string,WorkflowFileName:string,Range:string,SheetName:string,IsFirstRowHeaders:string,FilePath:string,dt:string,TargetObject:string,FilterRowsMode:string,SelectColumnsMode:string,Arg0:string,Arg1:string,DataTable:string,OutputDataTable:string,dt_triggersData:string,Values:string,in_ShouldStop:string,in_TriggerRow:string,in_DefaultExchangeService:string,in_OrchestratorMainFolderName:string,in_JobGUID:string,in_OrchestratorAuth:string,out_currentTriggerPriority:string,out_currentTriggerOrchestratorFolderPath:string,out_currentTriggerName:string,out_currentTriggerQueueName:string,in_AttachmentsType:string,in_From:string,in_MailFolder:string,in_SharedMailBox:string,in_BodyFilterContains:string,in_SubjectFilterContains:string,in_To:string,in_ExchangeService:string,DirectoryId:string,User:string,EmailAutodiscover:string,Server:string,ApplicationId:string,Domain:string,ExistingExchangeService:string,Top:string,CustomFolder:string,SharedMailbox:string,FilterByMessageIds:string,FilterExpression:string,io_BusinessRunningID:string,in_REFrameworkCorePath:string,in_BusinessProcess:string,in_BusinessDepartment:string,out_GeneralOrchestratorFolderPath:string,out_MainRPA_FilesDirectoryPath:string,out_EnvironmentName:string,in_ConfigFolder:string,Encoding:string,FileName:string,Content:string,CurrentIndex:string,out_Config:string,out_ProjectDescription:string,out_ProjectName:string,in_Config:string,logF_BusinessProcessName:string,in_TransactionNumber:string,io_TransactionItem:string,io_TransactionData:string,Expression:string,in_str_OrchestrartorMainFolderName:string,in_str_CustomOrchestratorQueueFolderPath:string,in_int_MaxTransactionToHandle:string,in_str_QueueName:string,in_OnlyBOI:string,in_DaysToReduce:string,in_int_MaxTransactionWithSysExcpInA_Row:string,in_boo_GlobalErrorCheck:string,in_boo_CheckUrgentJob:string,in_ProcessSetup:string,in_arr_str_TransactionKeysForAnalytics:string,QueueName:string,CustomOrchestratorQueueFolderPath:string,out_timespan_NextOK_Time:string,out_boo_IsNowBusinessAppsDownTime:string,out_boo_IsNowProjectDownTime:string,Process:string,ProcessName:string,in_SheetsKeysAndNames:string,in_WorkbookPath:string,in_BackgroundRunning:string,InstanceCachePeriod:string,ExistingWorkbook:string,WorkbookPath:string,Workbook:string,out_DataTables:string,Path:string,Reference:string,QueueType:string,TransactionItem:string,Collection:string,LogF_AllTransactionData:string,in_SpecificMail_UID:string,in_GetAttachments:string,in_BodyAsHTML:string,in_FolderToLookup:string,in_ShardMailbox:string,out_Mail:string,io_boo_TransactionPostponed:string,io_list_transactionBusinessExceptions:string,io_dic_AllTransactionData:string,io_currentTranItem:string,io_list_transactionSystemExceptions:string,in_int_HandledTransactionsCount:string,io_boo_SendTransactionToActionCenter:string,in_DateStamp:string,in_InputFolder:string,in_MailSettingsTable:string,in_CurrentTranMail:string,in_DateSetup:string,in_EmailSubject:string,in_EmailBody:string,in_RecipientEmail:string,in_ExcelValidationTemplate:string,in_TestDriveFolder:string,in_dic_AllTransactionData:string,in_TemplateFolder:string,in_strCurrency:string,in_currentTranItem:string,Exists:string,Destination:string,in_System:string,in_MaxHitsNum:string,in_UseTechnicalNames:string,in_CriteriaDictionary:string,in_UseListView:string,in_OutputFields:string,in_Layout:string,in_TableName:string,out_DataTable:string,in_TransactionCode:string,Selector:string,SearchScope:string,Window:string,DeselectAfter:string,Target_TimeoutMS:string,DelayMS:string,WaitForReady:string,Text:string,AlterIfDisabled:string,ClickBeforeTyping:string,DelayBetweenKeys:string,Activate:string,DelayBefore:string,SimulateType:string,SendWindowMessages:string,EmptyField:string,MouseButton:string,ClickType:string,OffsetY:string,SimulateClick:string,OffsetX:string,KeyModifiers:string,MaxNumberOfResults:string,DelayBetweenPagesMS:string,NextLinkSelector:string,ExtractMetadata:string,ApplicationWindow:string,Exception:string,in_ProjectName:string,in_Exception:string,in_dt_MailSettings:string,in_dt_ExceptionsSettings:string,in_BusinessProcessFolderPath:string,in_exception:string,in_isFrontProcess:string,in_needScreenshot:string,in_logLevel:string,in_screenshotsFolderPath:string,out_takenScreenshotNameOnly:string,out_takenScreenshotFullPath:string,in_StorageBucketName:string,in_TriggersSheetName:string,in_TriggersFilePath:string,in_SFViewColumnName:string,in_StorageDirctory:string,out_RemedySalesforceConnection:string,Directory:string,StorageBucketName:string,Filter:string,Recursive:string,in_RemedyConnection:string,in_StorageFileList:string,in_SalesforceEntityName:string,in_QueueName:string,in_QueueOrchFolder:string,in_Exceptions_IDs:string,in_Priority:string,in_ListViewName:string,in_DataRow:string,in_Reference:string,in_SpecificContent:string,in_OrchestratorFolderPath:string,in_QueueItemPriority:string,in_str_QueueItemReference:string,in_str_FolderPath:string,in_dic_QueueItemSpecificContent:string,in_SourceName:string,AppliesTo:string,in_Variant:string,in_MailSettingTable:string,in_InputFolderPath:string,in_CurrentTranItem:string,in_FileName:string,CursorMotionType:string,DelayAfter:string,Item:string,out_list_jobSystemExceptions:string,out_list_jobBusinessExceptions:string,out_Environment_FilesDirectoryPath:string,logF_jobGUID:string,in_TriggersDataFilePath:string,TriggerType:string,TriggerRow:string,SharedMailBox:string,MailFolder:string,in_AWSCredentialName:string,in_LogsFileNamePattern:string,in_BucketName:string,in_HistoryDaysNum:string,in_ObjectKeyFolder:string,in_Region:string,in_LogFolderPath:string,Output:string,ObjectKeyFolder:string,MachineName:string,LogFolderPath:string,in_FileContent_RowPrefixPattern:string,in_ADUserName:string,in_TempFolderPath:string,in_FilePath:string,in_MachineName:string,in_GeneralOrchFolderPath:string, ObjectKeyName:string,FileDate:string,FileFullPath:string,File:string,out_UploadedObjectDetails:string,in_ObjectKeyName:string,in_GeneralOrchestratorFolderPath:string,IAMRole:string,AccessKeyId:string,SecretAccessKey:string,Region:string,BucketName:string,IncludeDetails:string,in_ParallelChunkSize:string,io_list_jobSystemExceptions:string,in_DataTable:string,out_ListOfDictionaries:string,Messages:string,ExchangeService:string,out_Messages:string>>' but ' ' is found. (Service: null; Status Code: 0; Error Code: null; Request ID: null; Proxy: null)
This query ran against the "datalake" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: ae4c6949-4a43-4f0a-8bf7-0235211d056d