Insert into select

java

#1

Hi,

While running the query (see below) and passing the credentials for both the buckets, I am seeing this in the logs.

|00000000| 48 54 54 50 2f 31 2e 31 20 32 30 30 20 4f 4b 0d |HTTP/1.1 200 OK.|
|00000010| 0a 43 6f 6e 74 65 6e 74 2d 4c 65 6e 67 74 68 3a |.Content-Length:|
|00000020| 20 34 30 36 0d 0a 43 6f 6e 74 65 6e 74 2d 54 79 | 406..Content-Ty|
|00000030| 70 65 3a 20 61 70 70 6c 69 63 61 74 69 6f 6e 2f |pe: application/|
|00000040| 6a 73 6f 6e 3b 20 76 65 72 73 69 6f 6e 3d 32 2e |json; version=2.|
|00000050| 30 2e 30 2d 4e 31 51 4c 0d 0a 44 61 74 65 3a 20 |0.0-N1QL..Date: |
|00000060| 54 75 65 2c 20 32 39 20 4d 61 79 20 32 30 31 38 |Tue, 29 May 2018|
|00000070| 20 30 39 3a 30 38 3a 34 35 20 47 4d 54 0d 0a 0d | 09:08:45 GMT...|
|00000080| 0a 7b 0a 22 72 65 71 75 65 73 74 49 44 22 3a 20 |.{."requestID": |
|00000090| 22 31 37 38 32 30 35 61 36 2d 34 66 35 33 2d 34 |"178205a6-4f53-4|
|000000a0| 30 64 39 2d 62 32 64 38 2d 64 65 38 30 34 66 66 |0d9-b2d8-de804ff|
|000000b0| 62 66 38 32 63 22 2c 0a 22 73 69 67 6e 61 74 75 |bf82c",."signatu|
|000000c0| 72 65 22 3a 20 7b 22 69 64 22 3a 22 6a 73 6f 6e |re": {"id":"json|
|000000d0| 22 7d 2c 0a 22 72 65 73 75 6c 74 73 22 3a 20 5b |"},."results": [|
|000000e0| 0a 5d 2c 0a 22 65 72 72 6f 72 73 22 3a 20 5b 7b |.],."errors": [{|
|000000f0| 22 63 6f 64 65 22 3a 31 33 30 31 34 2c 22 6d 73 |"code":13014,"ms|
|00000100| 67 22 3a 22 55 73 65 72 20 64 6f 65 73 20 6e 6f |g":"User does no|
|00000110| 74 20 68 61 76 65 20 63 72 65 64 65 6e 74 69 61 |t have credentia|
|00000120| 6c 73 20 74 6f 20 72 75 6e 20 49 4e 53 45 52 54 |ls to run INSERT|
|00000130| 20 71 75 65 72 69 65 73 20 6f 6e 20 74 68 65 20 | queries on the |
|00000140| 67 70 63 32 20 62 75 63 6b 65 74 2e 20 41 64 64 |gpc2 bucket. Add|
|00000150| 20 72 6f 6c 65 20 71 75 65 72 79 5f 69 6e 73 65 | role query_inse|
|00000160| 72 74 20 6f 6e 20 67 70 63 32 20 74 6f 20 61 6c |rt on gpc2 to al|
|00000170| 6c 6f 77 20 74 68 65 20 71 75 65 72 79 20 74 6f |low the query to|
|00000180| 20 72 75 6e 2e 22 7d 5d 2c 0a 22 73 74 61 74 75 | run."}],."statu|
|00000190| 73 22 3a 20 22 73 74 6f 70 70 65 64 22 2c 0a 22 |s": "stopped",."|
|000001a0| 6d 65 74 72 69 63 73 22 3a 20 7b 22 65 6c 61 70 |metrics": {"elap|
|000001b0| 73 65 64 54 69 6d 65 22 3a 20 22 38 30 2e 30 30 |sedTime": "80.00|
|000001c0| 32 33 6d 73 22 2c 22 65 78 65 63 75 74 69 6f 6e |23ms","execution|
|000001d0| 54 69 6d 65 22 3a 20 22 38 30 2e 30 30 32 33 6d |Time": "80.0023m|
|000001e0| 73 22 2c 22 72 65 73 75 6c 74 43 6f 75 6e 74 22 |s","resultCount"|
|000001f0| 3a 20 30 2c 22 72 65 73 75 6c 74 53 69 7a 65 22 |: 0,"resultSize"|
|00000200| 3a 20 30 2c 22 65 72 72 6f 72 43 6f 75 6e 74 22 |: 0,"errorCount"|
|00000210| 3a 20 31 7d 0a 7d 0a                            |: 1}.}.         |

It says that I need to provide rights to insert but I am trying to copy data from one bucket to another by passing credentials of both bucket. Each bucket user has access to its own bucket. This is the code I am using:

	@GetMapping(value="/copyDataFromPmcToGpc2")
	public void copyDataFromPmcToGpc2() {
	 String statement = "UPSERT INTO gpc2 (key _k, value _v) SELECT meta().id _k, _v FROM pmc _v returning meta().id";
	 N1qlParams n1qlParams = N1qlParams.build().withCredentials(bucketName, bucketPassword).withCredentials(backup_bucketName, backup_bucketPassword);
	 N1qlQuery n1qlQuery = N1qlQuery.simple(statement, n1qlParams);
	 System.out.println("n1qlQuery: " + n1qlQuery);
	 Observable<AsyncN1qlQueryResult> product = mainCouchbaseTemplate.queryN1QL(n1qlQuery).doOnNext(res -> res.info().forEach(t -> System.out.println("N1qlMetrics: " + t)));
	 List<JsonObject> listOfJsons = product.flatMap(AsyncN1qlQueryResult::rows)
		.map(result -> {return result.value();})
		.toList()
		.toBlocking()
		.single();
	 System.out.println(listOfJsons);
	 
	}

Can someone shed light on how to copy data from one bucket to another using the UPSERT and SELECT in java.

PS: mainCouchbaseTemplate is the couchbase template for pmc bucket.

Thanks,
Himanshu


#2

What to do here depends on what version of Couchbase you are running, because permissions handling changed when we introduced Role-Based Access Control in 5.0.

Are you running a Couchbase version that doesn’t have RBAC, i.e. bucket passwords only?
If so, you should need to provide bucket/bucket-password for gpc2 and pmc.

If you are running a version that has RBAC, you should supply user-id/user-password for a user who has these three roles:
Query Insert (gpc2)
Query Update (gpc2)
Query Select (pmc)


#3

Actually, because of the RETURNING clause, you may need Query Select (gpc2) also.


#4

We are using RBAC. Thanks for the information.