2

I already have a j2ee application, which imports CSV files kept on some location on the MySql 5.7.17 server on a RHEL machine in Google Cloud platform using the MySql's Load data Infile command. Now there is some requirement due to which we have to stop putting the CSV on the RHEL machine and instead keep them in google bucket and access them from there for the "Load data infile" command. If we use the URL of the CSVs e.g. https://www.googleapis.com/storage/v1/b/.csv then how can this be done ? And I have to do this all within my java application. I am able to read the CSV files in java, but what file path should be set in the load data infile command ? Please note that the MySql setup is not the one provided by the google as an instance...

//Below code is for only reading files from bucket:


String PROJECT_ID = "tttttt-179995";
String PATH_TO_JSON_KEY = "E:/<SOMETHING>.json";
String BUCKET_NAME = "<SOMEWHERE>_uat";
String OBJECT_NAME = "<SOME>.csv";

StorageOptions options=null;
try {
    options = StorageOptions.newBuilder().setProjectId(PROJECT_ID).setCredentials(GoogleCredentials.fromStream(new FileInputStream(PATH_TO_JSON_KEY))).build();
} catch (FileNotFoundException e) {
    e.printStackTrace();
} catch (IOException e) {
    e.printStackTrace();
}

Storage storage = options.getService();
Blob blob = storage.get(BUCKET_NAME, OBJECT_NAME);

String fileContent = new String(blob.getContent());
System.out.println(fileContent);

Page<Blob> blobs = storage.list(BUCKET_NAME, BlobListOption.currentDirectory(),BlobListOption.prefix(OBJECT_NAME));

    for (Blob b : blobs.iterateAll()) {
        System.out.println(b.getSelfLink());

    }
siamsot
  • 1,501
  • 1
  • 14
  • 20
Keshri Nandan
  • 23
  • 3

1 Answers1

1

If we use the URL of the CSVs e.g. https://www.googleapis.com/storage/v1/b/.csv then how can this be done ?

Don't know. I wouldn't do it that way.

Here's how I would do it:

Step 1.

Read the CVS file from the bucket and store it as a file in the local file system. This would be done from Java using Java bindings for the Google APIs, or it could be done by running an external command (e.g. gsutil) from Java.

References:

Step 2.

Use JDBC to run LOAD DATA LOCAL INFILE '<pathname>' ... where <pathname> is the pathname of the file that you uploaded.

The pathname is given as a quoted string.

References:

Note that the upload would probably be faster if you upload from the server's file system, but it requires "root" database login, and you have the problem of getting the file into the server's file system.

... but what file path should be set in the load data infile command ?

Assuming you are talking about a LOAD DATA LOCAL INFILE, you can use either an absolute or relative pathname. Relative pathnames will be resolved relative to the (application) JVM's current directory; i.e. the current directory when the application was launched.

Stephen C
  • 698,415
  • 94
  • 811
  • 1,216
  • Thanks Stephen for the solution. We have to do this monthly activity of loading 48 files in our DB and the size of individual file ranges from approx 600 MB to 9 GB. Now from Bucket to App server and from App to DB server this is going to be lots of data in the network. Till now it used to be kept on the DB server itself, but now the DeOps team is not allowing access to any folder on DB to keep the files there. Let's see how this is going to effect the performance. – Keshri Nandan Aug 02 '19 at 09:56