CD210: Querying, Modeling, Tuning, andMigrating Data using N1QLCouchbase 4.1+Lab WorkbookLeo Schumanwith Rob Williams, Clarence Tauro, and Tony PiazzaJanuary 18, 2015
CD210: QUERYING, MODELING, TUNING, AND MIGRATING DATA USING N1QL 0 2 Module 1 Introduction 3 Lab 1 -‐ Set up and verify local learning environment 6 7 Module 2 Getting Familiar with Couchbase 9 Lab 1 -‐ Configure single-‐node Couchbase 4.1+ cluster 11 Lab 2 -‐ Install learning data using cbdocloader 12 15 Module 3 Working with N1QL and JSON 16 Lab 1 -‐ Launch Query Workbench or CBQ to create primary indexes 17 Demo -‐ Overview of Couchmusic data models 18 Lab 2 -‐ Access and edit documents via Couchbase Console documents filter 21 24 Module 4 Selecting, filtering, and indexing using N1QL 27 Lab 1 -‐ Selecting documents and limiting results 31 Lab 2 -‐ Using aliases, unnesting results, and concatenating values 35 Lab 3 -‐ Selecting by key or value, and accessing document keys via metadata 38 Lab 4 -‐ Creating and using secondary indexes, and using multiple filters 39 Lab 5 -‐ Querying ranges, ordering results, and explaining queries 40 Lab 6 -‐ Verifying index availability, counting values, and selecting distinct results using wildcards 41 42 Module 5 Modeling Data in JSON 43 Lab 1 -‐ Conceptualize entities in JSON 45 Lab 2 -‐ Conceptualize relationships in JSON 49 Lab 3 -‐ Conceptualize cardinality in JSON 52 53 Module 6 Working with JSON focused extensions in N1QL 57 Lab 1 -‐ Selecting for missing attributes, and grouping results by attributes 61 Lab 2 -‐ Operating on collections 64 Lab 3 -‐ Using functions in queries 66 69 Module 7 Joining and combining documents in N1QL 70 Lab 1 -‐ Join foreign documents by surrogate and natural key references 73 Lab 2 -‐ Combine data by nesting foreign documents 74 Lab 3 -‐ Implement the Lookup Key pattern using JOIN syntax 76 Lab 4 -‐ Implement a sub-‐query to filter results Lab 5 -‐ Implement a sub-‐query to combine results Module 8 Manipulating and migrating data using N1QL Lab 1 -‐ Insert, generate, and upsert dynamically generated documents from existing data Lab 2 -‐ Insert and delete a document Lab 3 -‐ Update with fixed or calculated values Lab 4 -‐ Merge value into document using sub-‐query Copyright 2016 - Couchbase Learning Services 1
Module 1 IntroductionCopyright 2016 - Couchbase Learning Services 2
Lab 1 - Set up and verify local learning environmentObjectives A (Optional) Install VirtualBox and build virtual machine from provided image B (Optional) Install Guest Additions to virtual machine C Review student files with InstructorA. (Optional) Install VirtualBox and build virtual machine from provided image Note, the following steps may be used if: (a) you are using your own system, (b) do NOT already have virtual machine software installed (e.g., VMWare), and (c) do NOT want to install Couchbase and the training files directly on your local drive. 1. Follow the instructions to download and install VirtualBox to your local system https://www.virtualbox.org/wiki/Downloads 2. Locate the following file from the Virtual Machine files provided by the Instructor. cd210-vm-[datestamp].ova 3. In VirtualBox, select File > Import Appliance, navigate to and open this file.4. Start the virtual machine, using these credentials: a. Username: Student b. Password: couchbase Copyright 2016 - Couchbase Learning Services 3
B. (Optional) Install Guest Additions and configure Display of virtual machine 5. In VirtualBox, select Devices > Insert Guest Additions CD Image. 6. Open a Terminal Window, and run this command to navigate to this directory: cd /media/student/VIRTUALBOXADDITIONS_5.0.10_[version]/ 7. In this directory, run this command regardless of your host operating system, as the virtual machine itself is running Linux: sudo ./VBoxLinuxAdditions.run Note, once run, you should have mouse support between the virtual machine and desktop. 8. In the virtual machine, open the Settings screen.Copyright 2016 - Couchbase Learning Services 4
9. In the Settings screen, open the Displays tool. In this tool, select and Apply a new screen resolution (e.g., 1400 x 1050) as needed for your host system.C. Review student files with Instructor 10. On the Desktop, open and review the contents of the CD210 folder with the instructor. These tools and files will be used in later labs. 11. On the Desktop, if you launch the web browser, do NOT start the Couchbase configuration wizard. It will be run to configure the local single-node Couchbase cluster, with needed settings, in a later lab.End of LabCopyright 2016 - Couchbase Learning Services 5
Module 2Getting Familiar with CouchbaseCopyright 2016 - Couchbase Learning Services 6
Lab 1 - Configure single-node Couchbase 4.1+ clusterObjectives A Install and configure Couchbase Server 4.1+ as a single node B Survey the Couchbase administration consoleA. Install and configure Couchbase Server 4.1+ as a single node 1. (Optional), if it is not already installed to your local system or virtual machine, download and install Couchbase Server 4.1+ for your operating system: http://www.couchbase.com/nosql-databases/downloads If necessary, refer to the installation instructions. 2. Open the Couchbase administration console at: http://localhost:8091 3. Run the Setup tool, review all settings and accept all defaults, except for changing these: ❏ Services: Data, Index, Query (verify “Index” and “Query” are checked) ❏ Data RAM quota: 2048 ❏ Index RAM quota: 512 ❏ Available Samples: beer-sample, gamesim-sample, travel-sample ❏ Per Node RAM quota: 512 ❏ Disable replicas (uncheck “Enable” in the Replicas section) ❏ Enable flush (check “Enable” in the Flush section) ❏ Agree to terms and conditions (check “I agree…”) ❏ Administrator account Password: couchbase Note, allow network activity for any requested services and observe console activity as sample data is loaded.Copyright 2016 - Couchbase Learning Services 7
3. After completing the Setup tool, you should see this screen, with variations depending on your operating system and system capacity:B. Survey the Couchbase administration console 4. In the administration console, navigate to and review each top-level screen. For a solid introduction to Couchbase technology, please watch the CB030 - Essentials of Couchbase NoSQL Technology online training course. Documentation is available at http://docs.couchbase.com.End of LabCopyright 2016 - Couchbase Learning Services 8
Lab 2 - Install learning data using cbdocloaderObjectivesA Create a bucket and load documents into Couchbase using cbdocloaderB Create primary indexes to support ad hoc queries (CREATE PRIMARY INDEX)A. Create a bucket and load documents into Couchbase using cbdocloader 1. Open a terminal window on your local machine or virtual machine, and navigate to:cd /home/student/Desktop/CD210/data-archives Note, if you are NOT using a virtual machine, the instructor will provide you the CD210 folder to copy to your local machine. 2. From the terminal, run the cbdocloader tool. Note, this tool is located in the Couchbase /bin directory. If you are using the CD210 virtual machine, this folder has been added to the operating system PATH variable, so its tools are available from any directory. Note, if you are NOT using the CD210 virtual machine, see your operating system detail, if needed, to add the correct folder to your PATH variable.Mac OSX /Applications/Couchbase Server.app/Contents/Resources/couchbase-core/bin/Windows C:\Program Files\Couchbase\Server\bin\Linux /opt/couchbase/bin/ 3. Use cbdocloader to load the documents into a data bucket to be named couchmusic1. Notice the specified bucket is created if it does not already exist. ❏ Username (-u): Administrator ❏ Password (-p): couchbase ❏ Node Address (-n): 127.0.0.1:8091 ❏ Bucket (-b): couchmusic1 ❏ RAM Quota in MB (-s): 512 ❏ JSON Document Archive: couchmusic1-countries.zipcbdocloader -u Administrator -p couchbase -n 127.0.0.1:8091 -b couchmusic1 -s 512 couchmusic1-countries.zip Copyright 2016 - Couchbase Learning Services 9
4. In the Couchbase admin UI, verify you’ve loaded 258 documents to couchmusic1. 5. Run the load-all.bash script in the /data-archives folder../load-all.bash Note, do to the limited system resources allocated to this virtual machine, this script may take as much as 25 minutes to finish. The instructor will continue while you wait. 6. Verify you have 280,299 documents in the couchmusic1 data bucket, and 280,322 in each of the couchmusic2 and couchmusic3 data buckets. 7. Open the Edit screen for each of these data buckets. 8. Disable replication (uncheck Enable) for each bucket, and Save your changes.Note, replication requires more than one node, else replication errors may appear. Thiscourse is taught using a single node cluster.End of Lab Copyright 2016 - Couchbase Learning Services 10
Module 3Working with N1QL and JSONCopyright 2016 - Couchbase Learning Services 11
Lab 1 - Launch Query Workbench or CBQ to createprimary indexesObjectives A Launch Query Workbench or cbq B Create primary indexes to support ad hoc queries (CREATE PRIMARY INDEX)A. Launch Query Workbench or cbq 1. Open a new terminal window. 2. Navigate to the folder below, and launch the Query Workbench.cd /home/student/Desktop/CD210/couchbase-query-workbench/./launch-cbq-gui.sh 3. Minimize this terminal window, and leave it open throughout the course. This version of the Couchbase Query Workbench runs locally from a terminal window. 4. In the web browser, navigate to this URL:http://localhost:8094Copyright 2016 - Couchbase Learning Services 12
5. (Optional) In a terminal window, use an explicit path to launch the Couchbase cbq tool./opt/couchbase/bin/cbqMac OSX /Applications/Couchbase Server.app/Contents/Resources/couchbase-core/bin/Windows C:\Program Files\Couchbase\Server\bin\Linux /opt/couchbase/bin/Note, due to a name conflict in this virtual machine, cbq must be launched using its full path.B. Create primary indexes to support ad hoc queries (CREATE PRIMARY INDEX) 6. You want to allow ad hoc N1QL queries to be run on the couchmusic1 bucket. In your preferred tool (Query Workbench or CBQ), run a CREATE PRIMARY INDEX statement on this bucket, using the global indexing service.CREATE PRIMARY INDEX ON couchmusic1 USING GSI;7. Repeat this step to create a primary index on couchmusic2 and couchmusic3. Copyright 2016 - Couchbase Learning Services 13
8. In Query Workbench, notice the data buckets now appear as fully queryable.9. In the Couchbase Console (Web UI), select the Index tab and review the indexes. Notice you can click to expand next to the bucket name to review the index definition.End of Lab Copyright 2016 - Couchbase Learning Services 14
Demo - Overview of Couchmusic data modelsObjectives A Review couchmusic document structuresA. Review couchmusic document structures 1. From the CD210 folder on the virtual machine desktop, or the student resources provided by the instructor, open these three documents: couchmusic-conceptual-data-model.png couchmusic1-data-model.pdf couchmusic2-data-model.pdf couchmusic3-data-model.pdf 2. Guided by the instructor, review the documents described for the couchmusic1, couchmusic2, and couchmusic3 data buckets.End of DemoCopyright 2016 - Couchbase Learning Services 15
Lab 2 - Access and edit documents via CouchbaseConsole documents filterObjectives A Use the Documents Filter to access and edit a documentA. Use the Documents Filter to access and edit a document 1. In the Couchbase Console, open the Documents view for the couchmusic1 bucket. 2. In the Documents screen, filter the displayed documents to start with keys containing the word “userprofile”. 3. Select Edit Document next to a document, and review the behavior of the document editor. Notice that invalid JSON is identified. Do NOT save your changes.End of LabCopyright 2016 - Couchbase Learning Services 16
Module 4 Selecting, filtering,and indexing using N1QLCopyright 2016 - Couchbase Learning Services 17
Lab 1 - Selecting documents and limiting resultsObjectives A Select all attributes from all documents, and limit selection (*, SELECT, LIMIT) B Select specific attributes from a document \"type\" (root-level attribute)A. Select all attributes from all documents, and limit selection (*, SELECT, LIMIT) 1. Verify that your terminal is running cbq against your local Couchbase instance. 2. Select all attributes from all documents in couchmusic1.SELECT *FROM couchmusic1; Note, because of the large document volume, do not wait for this query to complete. 3. Use Ctrl-C, or the equivalent command, to shut down cbq. Then, restart cbq. 4. Select all attributes from all documents in couchmusic1, but limit output to 1 result. Notice the signature, results, status, and metrics attributes of the output document.SELECT *FROM couchmusic1LIMIT 1;Note: you will virtually never select from the bucket-name, alone, without specifying atleast a top-level attribute from which your selected attribute(s) should be matched.Copyright 2016 - Couchbase Learning Services 18
B. Select specific attributes from a specific document \"type\" (root-level attribute) 5. Select all attributes from playlist documents in couchmusic1, limiting output to 1.SELECT *FROM couchmusic1.playlistLIMIT 1; 6. Select for the name attributes within playlist attributes in couchmusic1, limiting output to 10 results. Note that only name attributes contained in playlist documents are returned.SELECT nameFROM couchmusic1.playlistLIMIT 10;Copyright 2016 - Couchbase Learning Services 19
7. Select name, owner, and tracks attributes from playlist documents in couchmusic1, limiting output to 1 result.SELECT name, owner, tracksFROM couchmusic1.playlistLIMIT 1; Notice that objects with nested attributes - such as tracks - return as a full object; in this case, an array of string values representing specific track ID values.End of LabCopyright 2016 - Couchbase Learning Services 20
Lab 2 - Using aliases, unnesting results, andconcatenating valuesObjectives A Using aliases within queries to reference object attributes (AS) B Unnesting query results (UNNEST) C Concatenating values from object references ( || )A. Using aliases within queries to reference object attributes (AS) 1. Select all attributes for a userprofile document in couchmusic1, limit the results to 1 document. Note, due to a lack of filtering or secondary indexing for this query, it will likely take 15+ seconds or more, depending on system resources, to complete.SELECT *FROM couchmusic1.userprofileLIMIT 1;Copyright 2016 - Couchbase Learning Services 21
2. Select the firstName, lastName, created, and address attributes from userprofile documents, limiting output to 1 document. Alias the couchmusic1.userprofile document reference as u, and use this alias as a prefix to the selected attribute references.SELECT u.firstName, u.lastName, u.created, u.addressFROM couchmusic1.userprofile AS uLIMIT 1; Note, because you are not yet filtering for a specific document by key or value, the specific values returned here may vary.B. Unnesting query results (UNNEST) 3. Modify the prior query to unnest (“flatten”) the attributes of the address object, aliasing these results as a. Again, limit the results to 1 document.SELECT u.firstName, u.lastName, u.created, a.*FROM couchmusic1.userprofile AS uUNNEST address AS aLIMIT 1;Copyright 2016 - Couchbase Learning Services 22
C. Concatenating values from object references ( || ) 4. Modify the prior query to concatenate the firstName and lastName values, through the u alias representing the full userprofile object, and assign the results the alias fullName. Also, remove the AS keyword, to demonstrate to yourself that it's optional.SELECT u.firstName, u.lastName, a.*, u.firstName || \" \" || u.lastName AS fullNameFROM couchmusic1.userprofile uUNNEST address aLIMIT 1;End of LabCopyright 2016 - Couchbase Learning Services 23
Lab 3 - Selecting by key or value, and accessingdocument keys via metadataObjectives A Use keys to select specific documents (USE KEYS) B Filter for a specific document (WHERE), and get its key via metadata (META())A. Use keys to select specific documents (USE KEYS) 1. In the Couchbase console, open the Documents view for the couchmusic1 bucket. 2. In the Documents screen, filter the displayed documents to start with keys containing the word “userprofile”.Copyright 2016 - Couchbase Learning Services 24
3. Open the first userprofile document, and copy its key. 4. In cbq, select all attributes of this document by its key. Note, you must assign the key as a quoted string.SELECT *FROM couchmusic1USE KEYS \"userprofile::aahingeffeteness42037\"; The response time is dramatically faster, as no bucket scan is needed.Copyright 2016 - Couchbase Learning Services 25
5. Repeat the steps above to select all attributes for two documents, by their keys, in a single query. Use array syntax for the key list.SELECT *FROM couchmusic1USE KEYS [\"userprofile::aahingeffeteness42037\", \"userprofile::aahingheadwaiter24314\"];B. Filter for a specific document (WHERE), and get its key via metadata (META()) 6. Select the address object from userprofile documents in couchmusic1, along with the metadata for the source document, aliased as \"metadata\", where the email address is \"[email protected]\".SELECT address, META() as metadataFROM couchmusic1.userprofileWHERE email = \"[email protected]\"; Your result will include a metadata object, including the document ID. Note, due to lack of secondary indexing, this query will take 15+ seconds to run. Query specific indexing will be address in a later lab.End of Lab Copyright 2016 - Couchbase Learning Services 26
Lab 4 - Creating and using secondary indexes, andusing multiple filtersObjectives A Implement a secondary index for a specific document attribute (CREATE INDEX) B Modify a query to respond correctly to the design of a secondary index C Implement secondary index for attribute and filter (CREATE INDEX, WHERE)Note, this lab assumes you are already familiar with using the AND keyword in SQL, whenapplying multiple filter clauses to a query.A. Implement a secondary index for a specific document attribute (CREATE INDEX) 1. Re-run the last query from the prior lab, and notice its execution time.SELECT address, META() as metadataFROM couchmusic1.userprofileWHERE email = \"[email protected]\"; 2. Create a secondary index for userprofile.email attributes in the couchmusic1 bucket.CREATE INDEX userprofile_emailON couchmusic1(userprofile.email);Copyright 2016 - Couchbase Learning Services 27
3. Re-run the exact prior query, selecting address attributes by email address.SELECT address, META() as metadataFROM couchmusic1.userprofileWHERE email = \"[email protected]\"; Notice there is virtually no change in the execution time. It may even be slower.B. Modify a query to respond correctly to the design of a secondary index 4. Modify the query, so that its WHERE clause exactly matches the expression used in the CREATE INDEX statement. Run it again.Index statementCREATE INDEX userprofile_emailON couchmusic1(userprofile.email);Original query - slow even with indexSELECT address, META() as metadataFROM couchmusic1.userprofileWHERE email = \"[email protected]\";New query - fastSELECT userprofile.address, META() as metadataFROM couchmusic1WHERE userprofile.email = \"[email protected]\";Copyright 2016 - Couchbase Learning Services 28
C. Implement a secondary index for an attribute and filter (CREATE INDEX, WHERE) 5. Create a secondary index, named userprofile_by_state_and_status_active, for the state attribute in the address object of userprofile documents in couchmusic1. Filter the index to apply to documents where the status attribute of the userprofile has the value \"active\".CREATE INDEX userprofile_by_state_and_status_activeON couchmusic1(userprofile.address.state)WHERE userprofile.status = \"active\"; Note, to quickly view the full structure of a userprofile document, use the prior query, modifying it to select all (*) attributes of the document. SELECT * FROM couchmusic1 WHERE userprofile.email = \"[email protected]\";Copyright 2016 - Couchbase Learning Services 29
6. Test the new index by selecting all documents where the value of state is \"oregon\". Note, in couchmusic1, state values are all lower-case.SELECT *FROM couchmusic1WHERE userprofile.address.state = \"oregon\"; You should get 225 documents, but slowly. Why? You've not yet matched the index. 7. Modify the prior query to use an AND clause, to also filter the status attribute of the userprofile document to return only documents where the value is \"active\".SELECT *FROM couchmusic1WHERE userprofile.address.state = \"oregon\"AND userprofile.status = \"active\"; You should get 164 documents. This time, very quickly. Why?End of Lab Copyright 2016 - Couchbase Learning Services 30
Lab 5 - Querying ranges, ordering results, andexplaining queriesObjectives A Order the results of a query (ORDER BY) B Create an index to support a value range query (AND) C Introspect (EXPLAIN) and modify a query to use a corresponding indexA. Order the results of a query (ORDER BY) 1. Modify the last query of the prior lab to select only userprofile.email values, and order the results.SELECT userprofile.emailFROM couchmusic1WHERE userprofile.address.state = \"oregon\"AND userprofile.status = \"active\"ORDER BY userprofile.email;Copyright 2016 - Couchbase Learning Services 31
B. Create an index to support a value range query (AND) 2. Select all country documents in couchmusic1, and review the document structure.SELECT *FROM couchmusic1.country; 3. Select the name and population of all country documents with populations between one and two million. Note, you may use integer values, even though the data is serialized in scientific notation. Order the results by country name, in DESC order.SELECT name, populationFROM couchmusic1.countryWHERE population > 1000000 AND population < 2000000ORDER BY name DESC;Copyright 2016 - Couchbase Learning Services 32
4. Index the population attribute of country documents in couchmusic1, to improve the performance of this query.CREATE INDEX country_by_populationON couchmusic1(country.population); 5. Re-run the prior query, as written, and consider why the index created above does not improve its performance.C. Introspect (EXPLAIN) and modify a query to use a corresponding index 6. Add the keyword EXPLAIN to the start of the prior query, and re-run it, to extract information about its behavior.EXPLAINSELECT name, populationFROM couchmusic1.countryWHERE population > 1000000 AND population < 2000000ORDER BY name DESC; Notice that the by_population index is not being used, but a PrimaryScan instead. Also notice that the EXPLAIN statement returns query metadata, without running the query.7. Review the structure of the by_population index created above. couchmusic1(country.population); Copyright 2016 - Couchbase Learning Services 33
8. Leaving the EXPLAIN statement in place, modify the prior query, so that it will pick up and use the by_population index. Run the EXPLAIN query to get updated metadata.EXPLAINSELECT country.name, country.populationFROM couchmusic1WHERE country.population > 1000000 AND country.population < 2000000ORDER BY country.name DESC; Notice that the by_population index is now being used.9. Remove the EXPLAIN statement, and run the query. Results appear at indexed speed.End of Lab Copyright 2016 - Couchbase Learning Services 34
Lab 6 - Verifying index availability, counting values,and selecting distinct results using wildcardsObjectives A Verify available indexes in the Couchbase Console and by query B Count selected values (COUNT) C Using wildcard filters (LIKE) to select distinct values (DISTINCT)A. Verify available indexes in the Couchbase Console 1. Index the active attribute of userprofile documents in couchmusic1.CREATE INDEX userprofile_statusON couchmusic1(userprofile.status); 2. In the Couchbase Console, verify the index has been created. 3. In cbq, query all name values in the system:indexes table to view all indexes which have been built for the couchmusic1 bucket (\"keyspace\").SELECT nameFROM system:indexesWHERE keyspace_id = \"couchmusic1\";Copyright 2016 - Couchbase Learning Services 35
B. Count selected values (COUNT) 3. Use the COUNT keyword to count all userprofiles with \"active\" status in couchmusic1, as \"active_profiles\". Ensure your filter is written to match the index created above.SELECT COUNT(*) AS active_profilesFROM couchmusic1WHERE userprofile.status = \"active\"; Note, this is a current count as of the execution time, and may take a few seconds, even with indexing, depending on various factors. If sub-millisecond response is needed for a count, and a very small degree of data latency is acceptable, this is a prime use case for the MapReduce Views functionality, available in Couchbase 3.0+.C. Using wildcard filters (LIKE) to select distinct values (DISTINCT) 4. In Couchbase Console, verify that you created the userprofile_email index in a prior lab. 5. If not, create it using the following statement.CREATE INDEX userprofile_emailON couchmusic1(userprofile.email); Copyright 2016 - Couchbase Learning Services 36
If the index already exists, you will see this error: 6. To determine in which countries users are using Hotmail, select distinct countryCode values in address objects within userprofile documents, where the email attribute contains the value \"@hotmail.com\".SELECT DISTINCT userprofile.address.countryCodeFROM couchmusic1WHERE userprofile.email LIKE \"%hotmail.com\";Note: due to the userprofile_email index, you should see good performance from thisquery. At the same time, aggregate queries of this sort are good candidates forCouchbase MapReduce Views functionality, if a less than completely \"live\" result isneeded from the data bucket.End of Lab Copyright 2016 - Couchbase Learning Services 37
Module 5Modeling Data in JSONCopyright 2016 - Couchbase Learning Services 38
Lab 1 - Conceptualize entities in JSONObjectives A Review couchmusic document structures B Collaborate and conceptualize entities in JSONA. Review couchmusic document structures 3. From the student resources, open these three documents: couchmusic1-data-model.pdf couchmusic2-data-model.pdf couchmusic3-data-model.pdf 4. Review the documents described for the couchmusic1, couchmusic2, and couchmusic3 data buckets.B. Collaborate and conceptualize entities from documents 5. With a partner, list all entities described in the couchmusic1 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ 6. With a partner, list all entities described in the couchmusic2 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ 7. With a partner, list all entities described in the couchmusic3 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________End of Lab Copyright 2016 - Couchbase Learning Services 39
Lab 2 - Conceptualize relationships in JSONObjectives A Review couchmusic document structures B Collaborate and conceptualize relationships in JSONA. Review couchmusic document structures 1. From the student resources, open these three documents: couchmusic1-data-model.pdf couchmusic2-data-model.pdf couchmusic3-data-model.pdf 2. Review the documents described for the couchmusic1, couchmusic2, and couchmusic3 data buckets.B. Collaborate and conceptualize relationships from documents 3. With a partner, list all relationships described in the couchmusic1 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ 4. With a partner, list all relationships described in the couchmusic2 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ 5. With a partner, list all relationships described in the couchmusic3 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________End of Lab Copyright 2016 - Couchbase Learning Services 40
Lab 3 - Conceptualize cardinality in JSONObjectives A Review couchmusic document structures B Collaborate and conceptualize cardinality in JSONA. Review couchmusic document structures 1. From the student resources, open these three documents: couchmusic1-data-model.pdf couchmusic2-data-model.pdf couchmusic3-data-model.pdf 2. Review the documents described for the couchmusic1, couchmusic2, and couchmusic3 data buckets.B. Collaborate and conceptualize relationships from documents 3. With a partner, list all cardinalities described in the couchmusic1 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ 4. With a partner, list all cardinalities described in the couchmusic2 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ 5. With a partner, list all cardinalities described in the couchmusic3 documents. _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________End of Lab Copyright 2016 - Couchbase Learning Services 41
Module 6Working with JSON focused extensions in N1QLCopyright 2016 - Couchbase Learning Services 42
Lab 1 - Selecting for missing attributes, and groupingresults by attributesObjectives A Select document missing a specified attribute (IS MISSING, IS NOT MISSING) B Group attributes and order results to generate summary values (GROUP BY)A. Select document missing a specified attribute (IS MISSING) 8. Select the firstName, lastName, and address object of all userprofile documents in couchmusic1, where no gender attribute has been provided.SELECT firstName, lastName, addressFROM couchmusic1.userprofileWHERE gender IS MISSING;9. Consider whether an index would improve the speed of this query. How would you write a statement to index the gender attribute used to filter this query? Would a summary table of gender values mapped to corresponding document keys (aka, an \"index\") help determine which userprofile documents are missing this value?Copyright 2016 - Couchbase Learning Services 43
B. Group attributes and order results to generate summary values (GROUP BY) 10. How many user profiles exist for each country? Select the countryCode, and count the userprofile documents in general (*), in couchmusic1. Group the results by countryCode, and order the results in descending order by count.SELECT address.countryCode, COUNT(*) AS user_countFROM couchmusic1.userprofileGROUP BY address.countryCodeORDER BY user_count DESC; 11. Ask yourself: would an index improve execution time for this query?End of Lab Copyright 2016 - Couchbase Learning Services 44
Lab 2 - Operating on collectionsObjectives A Selecting where a specific value exists within a related array (WHERE, NOT, IN) B Selecting where a value satisfies an expression (WHERE, SATISFIES, END)A. Selecting where a specific value exists within a related array (WHERE, NOT, IN) 1. Review the structure of a userprofile document, and the collections it contains: address (object), favoriteGenres (array), phones (object), picture (object).Copyright 2016 - Couchbase Learning Services 45
2. Index userprofile documents by gender.CREATE INDEX userprofile_by_genderON couchmusic1(userprofile.gender); 3. Count the users who list their gender as \"female\", and also list \"folk\" as a favorite genre.SELECT COUNT(*) AS female_folk_fansFROM couchmusic1WHERE 'Folk' IN userprofile.favoriteGenresAND userprofile.gender = \"female\"; 4. Modify the prior query to count female users who do not like \"folk\".SELECT COUNT(*) AS female_NOT_folk_fansFROM couchmusic1WHERE 'Folk' NOT IN userprofile.favoriteGenresAND userprofile.gender = \"female\";Copyright 2016 - Couchbase Learning Services 46
B. Selecting where a value satisfies an expression (WHERE, SATISFIES, END) 5. Select 5 track documents from couchmusic1, and review their structure.SELECT *FROM couchmusic1.trackLIMIT 5;Copyright 2016 - Couchbase Learning Services 47
6. Select the artist, title, genre, and id from couchmusic1.tracks, where the results satisfy an expression requiring that a user named \"conwormish43746\" has given the track a 5 rating.SELECT artist, title, genre, idFROM couchmusic1.trackWHERE ANY r IN ratings SATISFIES r.username = \"conwormish43746\" AND r.rating = 5 END;End of Lab Copyright 2016 - Couchbase Learning Services 48
Lab 3 - Using functions in queriesObjectives A Use functions to calculate values from document data (MIN, STR_TO_MILLIS) B Use mathematical expressions to calculate values ( - , / ) C Truncate results to return an integer value (TRUNC())A. Use functions to calculate values from document data (MIN, STR_TO_MILLIS) 1. Convert dateOfBirth values in userprofile documents to milliseconds, and select for the smallest value, to determine the oldest user (whose birth date is closest to the epoch, and therefore the smallest number of milliseconds).SELECT MIN(STR_TO_MILLIS(dateOfBirth)) AS oldest, META() AS metaFROM couchmusic1.userprofile;Copyright 2016 - Couchbase Learning Services 49
Search
