Exporting GSI indexes

SQL++
1

Is there a way to export/import indexes created in cbq?
The cb_backup/cb_restore does only the view indexes but not the GSI’s

2

I don’t believe there’s an explicit way to restore indexes like that, at the moment. However, what you could try is running:

SELECT * FROM system:indexes;

which will give you a JSON representation of the indexes, including the name and the fields indexed. You could potentially reconstruct the pure N1QL from that. Given that the CB UI shows the indexes and the N1QL used to create it, I would imagine there’s something in there to do what you want already, though.

3

Yes I can query system:indexes but that is a fairly comlicated way of reconstructing an index, especially if it’s a complex one.
I can rerun all the indexes but I have 20ish at the moment, and I don’t think saving them to a txt file is the best way for exporting :smile:
A tool for which takes SELECT * FROM system:indexes; result as an input and generates N1QLs would actually be quite nice :smile:

4

Hi @tony_mrakovcic. Any update? Looking for the same functionality here.

5

I agree. We should have a simple way to export the index definition.

Since we’re showing this in the UI, there’s probably a REST API showing the CREATE INDEX statement returned by the indexer. @eben: Can you please share how UI is getting/constructing the CREATE INDEX definition?

@anil, @chaitra.ramarao: please open a tooling feature request for this.

6

curl -v Administrator:yourpassword@indexeripaddress:9102/getIndexStatus

1 Like
7

There is a similar REST API endpoint on :8091/indexStatus that returns data that appears as follows:

   "indexes": [
        {
            "bucket": "default",
            "definition": "CREATE PRIMARY INDEX `#primary` ON `default`",
            "hosts": [
                "127.0.0.1:9091"
            ],
            "id": 7597221357541960732,
            "index": "#primary",
            "progress": 100,
            "status": "Ready",
            "storageMode": "plasma"
        },
        {
            "bucket": "beer-sample",
            "definition": "CREATE INDEX `beer_brewery_id` ON `beer-sample`(`brewery_id`) WHERE (`type` = \"beer\")",
            "hosts": [
                "127.0.0.1:9091"
            ],
            "id": 1857501900942906209,
            "index": "beer_brewery_id",
            "progress": 100,
            "status": "Ready",
            "storageMode": "plasma"
        }

and so on.

1 Like
8

p.s. The newer version of the backup tool, cbbackupmgr, does backup and restore GSI indexes, though you still need to build them after the restore.

9

Now that a REST API can return the index definitions, you can use the N1QL CURL() function to retrieve and manipulate them in a query.

See: https://blog.couchbase.com/curl-comes-n1ql-querying-external-json-data/

10

@keshav_m - that’s a cool idea, but a tricky query to write. Could some one from the query team come up with an example?

11

As post mentioned you can create whitelist

SELECT CURL("http://Administrator:passXXX@127.0.0.1:8091//indexStatus").indexes[*].definition As indexes;
    "results": [
        {
            "indexes": [
                "CREATE PRIMARY INDEX `#primary` ON `default`",
                "CREATE INDEX `ix100` ON `default`(`c0`,`c1`,`c2`,`c3`,`c4`)"
            ]
        }
    ]
1 Like
12

The following can also be used to get the list of Create Index statements(including any WITH clause that was originally specified except the node list see MB-26184).

curl -v Administrator:yourpassword@indexeripaddress:9102/getIndexStatement

The officially supported way to backup/restore is using cbbackupmgr. This tool will also try to retain the index topology upon restore if the new cluster configuration is same as the original one.

13

Here is similar way using system:indexes syatem tables.

SELECT 'create index ‘|| name ||’ on '|| keyspace_id , index_key
FROM system:indexes
WHERE keyspace_id=‘sessiondb_om’
LIMIT 10;

it should be like below we need to remove field name from output using our local editors and add ; at end and run this with cbq -f option to execute it. or add the with differ build or replica number at end using || in query what ever you want. but this will not work for all type index ddls.

image
image1124×310 21 KB