NoSQL Tutorial

 in
A comprehensive look at the NoSQL database.
More Insertion: The Lists

NoSQL can handle data in different way, called list format. A sample table may be:

CODE    ACM003
NAME    Bart Simpson
PHONE   1-555-5432-321
EMAIL   bart@springfield.org
CODE    ACM004
NAME    Wiley The Coyote"
PHONE   1-800-ILLGETIT
EMAIL   wiley@looneys.com

Yes, you're right! This is the same way the edit command displays data. Although list tables aren't performing at all, in my opinion they are a good way to insert new data into tables. It's handy creating a program that can output this fashion. An example is shown in Listing 1.

Okay, this is not a “state of the art” shell program, but this example may show that the complete operation is easy with every language, even the shell.

There are a couple of things I would like to emphasize about the above code. How can a list be merged with a real table? The command listtotable does the job for you, as you probably guessed looking at the pipe, converting the list format to the table one. A reverse command, tabletolist, exists as well.

Please notice the beginning and ending newlines, as well as the tabs between field name and value, which appear in the print statement: these are required in order to create the correct list structure.

The list structure, as well as table structure, are well documented in the Chapter 2 of the NoSQL reference you will find in /usr/local/doc/nosql/doc.

Getting More from NoSQL

Now, let's have a business example? A complete catalog was created using a NoSQL table (see the catalog.rdb file below) and published on the Web dynamically. Every two days, we receive orders from our customers that they have usually created with Excel and exported, at our request, in a coma-separated file.

PRID    DESC                             PRICE
------  -------------------------       ------
PRD001  Acme glue for RoadRunners        30.00
PRD002  Acme TNT                        150.00
PRD003  Carrots                           5.00
PRD004  Acme toolbox                     75.00
The file (sample_order.txt below) we receive has the following
format: requester's unique code, Product ID, Quantity.
ACM004,PRD001,5
ACM004,PRD002,30
ACM004,PRD004,1

Now from a shell or command line we run:

export TMPFILE=`mktemp -q /tmp/$0.XXXXXX` ; cat sample_order.txt | \
perl -e 'print "CODE\tPROD\tQTY\n"; print "----\t----\t---\n"; \
while(
catalog.rdb | \
nosql addcol SUBTOTAL | nosql compute 'SUBTOTAL = QTY*PRICE' > $TMPFILE ; \
echo "Please bill to:" ; echo "---------------" ; echo ""; cat
$TMPFILE |\
nosql join -j CODE - customer.rdb | nosql column NAME PHONE EMAIL | \
nosql body | head -1 ; echo "";echo "" ; cat $TMPFILE | nosql rmcol CODE | \
nosql print -w; echo ""; echo -n "Total due: "; cat $TMPFILE | \
nosql subtotal -T SUBTOTAL | nosql body ; rm $TMPFILE
and our output is:
Please bill to:
---------------
Wiley The Coyote        1-800-ILLGETIT  wiley@looneys.com
PROD   QTY DESC                       PRICE SUBTOTAL
------ --- ------------------------- ------ --------
PRD001   5 Acme glue for RoadRunners  30.00      150
PRD002  30 Acme TNT                  150.00     4500
PRD004   1 Acme toolbox               75.00       75
Total due: 4725
This result may be sent via e-mail to our logistic people. Not so bad for a five-minute single shell command, is it?

I know it's a bit hermetic, so let's have a closer look: the explanation is divided in four sections to be easily read. I will keep out the echo commands which are quite obvious.

Section 1: Extracting Useful Data From the Received File
  • export TMPFILE=`mktemp -q /tmp/$0.XXXXXX`: exporting environment variable TMPFILE that contains a runtime generated tempfile.

  • cat sample_order.txt: get as input the file we received.

  • perl -e 'print "CODE\tPROD\tQTY\n"; print "-----\t----\t---\n"; while(<STDIN>) { s/,/\t/g; print };`: prints a NoSQL compliant header, composed of CODE, PROD and QTY. Then, it prints the table received from STDIN, substituting a coma (,) with a tab (\t), in order to get the correct table structure.

  • nosql join -j PROD - catalog.rdb: joins the table read from STDIN (the - character) and catalog.rdb using the PROD (product ID) column.

  • nosql addcol SUBTOTAL: now a column SUBTOTAL is added to the resulting table.

  • nosql compute 'SUBTOTAL = QTY*PRICE' > $TMPFILE: calculates the SUBTOTAL column by multiplying quantity and price columns. It then redirects the resulting table to the previous calculated temporary file.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

dbms

srikanth.v's picture

my query is i want the thing as price * qty should give the amount and the total should be calculated for that what is the query in oracle sql(structured query language).

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState