LinkedIn
Twitter
I have a very weird problem trying to create an EXT new-line formatted JSON table. I say weird because I was able to create the same exact EXT table in another Project-Dataset just a week ago.
I created a NEW Project and Dataset and when I try to create the table I get this:
"Failed to create table: Invalid field name "completed-in". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 300 characters long."
The NEW Dataset looks like this:
The ORIGINAL Dataset (where I was able to create the table) looks as follows:
The ORIGINAL EXT Table looks like this:
Why was I able to create the same exact EXT Table (not a single change) in the original dataset but now I cannot create it in a NEW Project/Dataset?
Totally perplexed...
Solved
1 ACCEPTED SOLUTION
Ok, I am going to close this issue. I believe that this does indeed have to do with changes that have been made since creating the original Project. There is no other explanation that makes sense.
Below is how I was finally able to create the EXT JSON table. NOTE: Fortunately, I dont need the col with the offending '-' char. If I did, then I dont this would be possible since you need to match the schema with the fields in the JSON in order to query those values.
CREATE OR REPLACE EXTERNAL TABLE `ford-analytics-408918.Daily_JSON_Logs.day_01`
(
meta STRUCT
<
count INTEGER--,
--`completed-in` INTEGER # CANNOT MATCH THIS FIELD
>,
data ARRAY <
STRUCT <
country STRING,
language STRING,
entityTags ARRAY <STRING>,
modelTags ARRAY <STRING>,
makeTags ARRAY <STRING>,
sa STRING,
messageType STRING,
classTags ARRAY <STRING>,
published TIMESTAMP,
author STRING,
content STRING,
link STRING,
queue STRING,
userTags ARRAY <STRING>,
title STRING,
dataSource STRING,
threadId STRING,
dataCategory STRING,
id STRING
>
>
)
OPTIONS (
format = 'NEWLINE_DELIMITED_JSON',
uris = ['https://drive.google.com/open?id=1YhNpquVz9YqU8PODkL7EiTn2NOTh7eYU'],
ignore_unknown_values = TRUE
);And now, querying the table yeilds a valid result set:
Thank you for your help with this matter.
Happy Holidays! 🎄⛄🎅🏼🤶🦌❄
