AWS athena + Glue initial query issue

Question

Trying to create a first query to a JSON file stored in S3 using Athena and Glue I get the following error:

Your query has the following error(s):

SYNTAX_ERROR: line 1:8: SELECT * not allowed in queries without FROM clause

This query ran against the "peds" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: a0f8c091-5738-423e-ac0f-7c4a6002273b.

My query is the test query that is automatically generated by the console when clicking on preview table and does have a FROM clause. This is the query:

SELECT * FROM "peds"."data" limit 10

I already tried the followings:

1. I opened my bucket permissions to make sure its not a security issue
2. found online that there is problems with hyphens in names and made sure I dont have that issue
3. made sure that the bucket that Athena writes to is in the same region as my Athena service.

Any ideas what am I doing wrong?

** EDIT ** Sample json (the actual json is larger but should have similar schema):

{ "PatentBulkData" : [ {"patentCaseMetadata":{"applicationNumberText":{"value":"04558530","electronicText":"04558530"},"filingDate":"1956-01-11","applicationTypeCategory":"Utility","partyBag":{"applicantBagOrInventorBagOrOwnerBag":[{"primaryExaminerOrAssistantExaminerOrAuthorizedOfficer":[{"name":{"personNameOrOrganizationNameOrEntityName":[{"personFullName":"SCHOFER, JOSEPH"}]}}]},{"partyIdentifierOrContact":[{"name":{"personNameOrOrganizationNameOrEntityName":[{"personStructuredName":{"lastName":"FRENCH, HUGHES & DOESCHER"}}]},"postalAddressBag":{"postalAddress":[{"postalStructuredAddress":{"addressLineText":[{"value":"SUITE 1107"},{"value":"1825 K STREET, N. W."}],"cityName":"WASHINGTON","geographicRegionName":[{"value":"DC"}],"countryCode":"US","postalCode":"20006"}}]}}]}]},"groupArtUnitNumber":{"value":"1505","electronicText":"1505"},"applicationConfirmationNumber":"9850","patentClassificationBag":{"cpcClassificationBagOrIPCClassificationOrECLAClassificationBag":[{"ipOfficeCode":"US","mainNationalClassification":{"nationalClass":"526","nationalSubclass":"351000"}}]},"businessEntityStatusCategory":"UNDISCOUNTED","firstInventorToFileIndicator":"Other","inventionTitle":{"content":["SOLID POLYMERS OF OLEFINS AND PRODUCTION OF SUCH POLYMERS"]},"applicationStatusCategory":"Patented Case","applicationStatusDate":"1983-01-12","officialFileLocationCategory":"FILE REPOSITORY (FRANCONIA)","officialFileLocationDate":"2019-12-09","patentGrantIdentification":{"patentNumber":"4376851","grantDate":"1983-03-15"}},"prosecutionHistoryDataBag":{"prosecutionHistoryData":[{"eventDate":"2001-09-21","eventCode":"SETS","eventDescriptionText":"Set Application Status"},{"eventDate":"2000-03-01","eventCode":"FOND","eventDescriptionText":"Case Found"},{"eventDate":"2000-03-01","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1987-02-20","eventCode":"TMOS","eventDescriptionText":"Termination of Official Search"},{"eventDate":"1987-01-12","eventCode":"SRCH","eventDescriptionText":"Official Search Conducted"},{"eventDate":"1986-12-19","eventCode":"TMOS","eventDescriptionText":"Termination of Official Search"},{"eventDate":"1986-12-18","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1986-11-07","eventCode":"SRCH","eventDescriptionText":"Official Search Conducted"},{"eventDate":"1986-11-04","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1986-06-20","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1986-05-01","eventCode":"TMOS","eventDescriptionText":"Termination of Official Search"},{"eventDate":"1986-05-01","eventCode":"SRCH","eventDescriptionText":"Official Search Conducted"},{"eventDate":"1986-04-23","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1985-10-01","eventCode":"SRCH","eventDescriptionText":"Official Search Conducted"},{"eventDate":"1985-09-24","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1985-06-20","eventCode":"SRCH","eventDescriptionText":"Official Search Conducted"},{"eventDate":"1985-06-20","eventCode":"LOST","eventDescriptionText":"Case Reported Lost"},{"eventDate":"1982-11-24","eventCode":"N084","eventDescriptionText":"Issue Fee Payment Verified"},{"eventDate":"1982-11-19","eventCode":"MN/=.","eventDescriptionText":"Mail Notice of Allowance"},{"eventDate":"1982-11-19","eventCode":"N/=.","eventDescriptionText":"Notice of Allowance Data Verification Completed"},{"eventDate":"1982-04-30","eventCode":"MCTNF","eventDescriptionText":"Mail Non-Final Rejection"},{"eventDate":"1982-04-30","eventCode":"CTNF","eventDescriptionText":"Non-Final Rejection"},{"eventDate":"1982-11-18","eventCode":"DOCK","eventDescriptionText":"Case Docketed to Examiner in GAU"}]},"assignmentDataBag":{"assignmentData":[{"reelNumber":"13158","frameNumber":"318","documentReceivedDate":"2002-08-09","recordedDate":"2002-08-05","mailDate":"2002-10-10","pageTotalQuantity":4,"conveyanceText":"RELEASE OF SECURITY AGREEMENT","assignorBag":{"assignor":[{"executionDate":"2002-04-30","contactOrPublicationContact":[{"name":{"personNameOrOrganizationNameOrEntityName":[{"value":"BANKAMERICA BUSINESS CREDIT, INC., AS AGENT"}]}}]}]},"assigneeBag":{"assignee":[{"contactOrPublicationContact":[{"name":{"personNameOrOrganizationNameOrEntityName":[{"value":"HYUNDAI MOTOR AMERICA"}]},"postalAddressBag":{"postalAddress":[{"postalAddressText":[{"sequenceNumber":"1","value":"10550 TALBERT AVENUE"},{"sequenceNumber":"2","value":"FOUNTAIN VALLEY CALIFORNIA 92708"}]}]}}]}]},"correspondenceAddress":{"partyIdentifierOrContact":[{"name":{"personNameOrOrganizationNameOrEntityName":[{"value":"JENKENS & GILCHRIST, P.C."}]},"postalAddressBag":{"postalAddress":[{"postalAddressText":[{"sequenceNumber":"1","value":"ANDRE M. SZUWALSKI"},{"sequenceNumber":"2","value":"1445 ROSS AVENUE, STE. 3200"},{"sequenceNumber":"3","value":"DALLAS, TX  75202-2799"}]}]}}]},"sequenceNumber":"1"}],"assignmentTotalQuantity":1},"st96Version":"V3_1","ipoVersion":"US_V8_0"}
] }

Schema:

Answer 1

I uploaded the sample JSON that you proivded to S3 and ran a crawler which gave me below definition.

CREATE EXTERNAL TABLE `test`(
  `patentbulkdata` array<struct<patentcasemetadata:struct<applicationnumbertext:struct<value:string,electronictext:string>,filingdate:string,applicationtypecategory:string,partybag:struct<applicantbagorinventorbagorownerbag:array<struct<primaryexaminerorassistantexaminerorauthorizedofficer:array<struct<name:struct<personnameororganizationnameorentityname:array<struct<personfullname:string>>>>>,partyidentifierorcontact:array<struct<name:struct<personnameororganizationnameorentityname:array<struct<personstructuredname:struct<lastname:string>>>>,postaladdressbag:struct<postaladdress:array<struct<postalstructuredaddress:struct<addresslinetext:array<struct<value:string>>,cityname:string,geographicregionname:array<struct<value:string>>,countrycode:string,postalcode:string>>>>>>>>>,groupartunitnumber:struct<value:string,electronictext:string>,applicationconfirmationnumber:string,patentclassificationbag:struct<cpcclassificationbagoripcclassificationoreclaclassificationbag:array<struct<ipofficecode:string,mainnationalclassification:struct<nationalclass:string,nationalsubclass:string>>>>,businessentitystatuscategory:string,firstinventortofileindicator:string,inventiontitle:struct<content:array<string>>,applicationstatuscategory:string,applicationstatusdate:string,officialfilelocationcategory:string,officialfilelocationdate:string,patentgrantidentification:struct<patentnumber:string,grantdate:string>>,prosecutionhistorydatabag:struct<prosecutionhistorydata:array<struct<eventdate:string,eventcode:string,eventdescriptiontext:string>>>,assignmentdatabag:struct<assignmentdata:array<struct<reelnumber:string,framenumber:string,documentreceiveddate:string,recordeddate:string,maildate:string,pagetotalquantity:int,conveyancetext:string,assignorbag:struct<assignor:array<struct<executiondate:string,contactorpublicationcontact:array<struct<name:struct<personnameororganizationnameorentityname:array<struct<value:string>>>>>>>>,assigneebag:struct<assignee:array<struct<contactorpublicationcontact:array<struct<name:struct<personnameororganizationnameorentityname:array<struct<value:string>>>,postaladdressbag:struct<postaladdress:array<struct<postaladdresstext:array<struct<sequencenumber:string,value:string>>>>>>>>>>,correspondenceaddress:struct<partyidentifierorcontact:array<struct<name:struct<personnameororganizationnameorentityname:array<struct<value:string>>>,postaladdressbag:struct<postaladdress:array<struct<postaladdresstext:array<struct<sequencenumber:string,value:string>>>>>>>>,sequencenumber:string>>,assignmenttotalquantity:int>,st96version:string,ipoversion:string>> COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
WITH SERDEPROPERTIES ( 
  'paths'='PatentBulkData') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test010010/test/'

When I ran a query in Athena it gave me below result fine with out any issues :

Create table using this create table statement on your JSON data by pointing to S3 location. If this gives any schema error then the issue has to be the actual JSON file than what you have shared in your question.