I agree that this problem is somehow associated with privileges, either
associated to the access of the bucket/file that I want to LOAD AND/OR
privileges within the database instance.
From what I’ve tried, I’m thinking it’s more likely related to the
inability for the database user account to access the file in the bucket …
but I can’t figure out what the problem is and if it’s related to the
bucket, the privileges assigned to the user or something else.
First the bucket
Bucket is called ‘stonewaterdata’ and ‘Requestor Pays: off”
I have access to that bucket from root and my own id (pasta)
I can upload/download files to this bucket when I use the SQL console
IMPORT and EXPORT gui while connected to the database as my id (pasta) … so
I know I can read/write to the bucket.
However if I try to execute this LOAD from the terminal when connected to
my database can’t.
I’ve tried from both my user (pasta) that I believe I have properly set up
and from root.
NOTE: the file designation below (one commented out). I’ve located the
file in both the bucket and tried using the gs://stonewater…. Bucket
designation and the file is also in my /home/pasta directory (permissions
on the directory and file we changed (chmod 777 ….. ) to allow maximum
access given the troubles I’ve been having . Neither work.
I’ve also been able to “copy” the file from the bucket to my /home
directory … all done just to verify access to the bucket/file.
LOAD DATA LOCAL INFILE
/ ‘gs://stonewaterdata/Jobs_Report_1_17_2023.csv’ /
‘/home/pasta/jobs_report_1_17_2023.csv’
INTO TABLE contracts.Jobs_Report
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘"’ LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS
(Current_milestone, Job_Name, Contact_Name, Contact_Email, Phone_Number,
Location_Address, Lead_Source, Contract_Total, Primary_Salesperson,
@Lead_Date, @Prospect_Date, @Approved_Date, @Completed_Date,
@Invoiced_Date, @Closed_Date, Job_Name_Url,
@Street, @City, @State, @Zip, @Country)
SET
/* from left go right stop at the first comma for street */
Street = SUBSTRING_INDEX(location_address,‘,’, 1),
/* from left go right stop at the second comma then go from that comma
back one comma for city */
City = SUBSTRING_INDEX(SUBSTRING_INDEX(location_address, ‘,’, 2),‘,’,-1),
/* from right go left stop at the first comma then go two space right for
state */
State = SUBSTRING_INDEX(SUBSTRING_INDEX(location_address,‘,’, -1),’ ', 2),
/* from right go left to the second space then go one space right for zip
*/
ZIP = SUBSTRING_INDEX(SUBSTRING_INDEX(location_address,’ ', -2), ’ ', 1),
/* from right go left to the first space for country */
Country = SUBSTRING_INDEX(location_address,’ ', -1),
Lead_Date = str_to_date(@Lead_Date, “%c/%d/%y”) ,
Prospect_Date = str_to_date(@Prospect_Date, “%c/%d/%y”) ,
Approved_Date = str_to_date(@Approved_Date, “%c/%d/%y”),
Completed_Date = str_to_date(@Completed_Date, “%c/%d/%y”),
Invoiced_Date = str_to_date(@Invoiced_Date, “%c/%d/%y”),
Closed_Date = str_to_date(@Closed_Date, “%c/%d/%y”)
;
I’ve tried both using LOCAL and removing LOCAL from the LOAD DATA statement
as I believe I read you don’t support the LOAD command without LOCAL.
I’ve checked GRANTS and permissions and privileges in the database for both
my id and root and as best as I can tell they are all set appropriately …
but it is possible I’ve missed something.
I’m lost and ready to drop Google Cloud and SQL for this project if I can’t
make progress soon.
What can you recommend?
–
IMPORTANT: The contents of this email and any attachments are confidential.
They are intended for the named recipient(s) only. If you have received
this email by mistake, please notify the sender immediately and do not
disclose the contents to anyone or make copies thereof.