1 00:00:00,730 --> 00:00:01,960 My name is Matthew Graham. 2 00:00:01,960 --> 00:00:06,370 And this is the last module in a series of six on databases. 3 00:00:06,370 --> 00:00:11,310 In previous modules, we've talked quite extensively about relational databases, 4 00:00:11,310 --> 00:00:15,500 the data model behind it, and the supporting technologies. 5 00:00:15,500 --> 00:00:18,950 I would maintain that this is because a lot 6 00:00:18,950 --> 00:00:23,630 of big data analytics can still be done on relational databases. 7 00:00:24,850 --> 00:00:29,790 However, there comes a point where a relational database is not sufficient for 8 00:00:29,790 --> 00:00:32,390 the type of problem that you're trying to deal with. 9 00:00:34,450 --> 00:00:38,610 Alex Szalay has said that anything beyond 300 terabytes, 10 00:00:38,610 --> 00:00:42,990 is generally difficult when you're trying to do any form of data management. 11 00:00:42,990 --> 00:00:47,620 And relational database systems 12 00:00:49,320 --> 00:00:54,220 are tuned for small but frequent read-write transactions, or 13 00:00:54,220 --> 00:00:59,920 large batch transactions with unfrequent write accesses. 14 00:01:02,620 --> 00:01:06,192 This tends to mean that they're scalable in terms of your dataset size. 15 00:01:06,192 --> 00:01:12,280 Or read-write concurrency, you can always spread the database onto multiple servers, 16 00:01:12,280 --> 00:01:16,430 and clusters of services, distributed clusters of services in some cases. 17 00:01:17,740 --> 00:01:22,410 If the types of problems that you're wanting to deal with are still, that you 18 00:01:22,410 --> 00:01:27,060 have small but frequent read write transactions, or large batch transactions. 19 00:01:27,060 --> 00:01:32,700 Number of data, amount of data, greater number of users doesn't really matter. 20 00:01:32,700 --> 00:01:36,750 Problem comes, however, when you move outside of that sweet spot. 21 00:01:36,750 --> 00:01:42,010 When you've actually got more reads happening than the database system 22 00:01:42,010 --> 00:01:47,280 can actually cope with or, or more writes are being put onto the data. 23 00:01:48,700 --> 00:01:51,140 If you have too many joins going on, 24 00:01:51,140 --> 00:01:54,470 because of the way that you've structured your database. 25 00:01:54,470 --> 00:01:56,680 Even if you have normalized it. 26 00:01:56,680 --> 00:02:00,820 Well, particularly if you've normalized it really because that encourages joins. 27 00:02:00,820 --> 00:02:02,910 That can cause performance issues. 28 00:02:02,910 --> 00:02:05,820 You find that the queries become slower. 29 00:02:07,400 --> 00:02:12,760 Also, if you are doing a lot of complex queries on the database making 30 00:02:12,760 --> 00:02:18,260 use of a lot of stored procedures and possibly even views, where 31 00:02:18,260 --> 00:02:22,740 there's a lot of server site computation required to support a particular query, 32 00:02:22,740 --> 00:02:27,210 then you can find that you get performance problems with a relational database. 33 00:02:29,860 --> 00:02:35,010 Fortunately, as we've seen in Module 2 when we talked about different types of 34 00:02:35,010 --> 00:02:42,310 data models, there are other ways of representing your data and therefore, 35 00:02:42,310 --> 00:02:46,180 different technological solutions which may be more appropriate for 36 00:02:46,180 --> 00:02:47,430 the type of problem that you're facing. 37 00:02:49,400 --> 00:02:52,640 Different types of data store that you may consider if you have 38 00:02:52,640 --> 00:02:54,580 a scaling problem with your data. 39 00:02:57,330 --> 00:03:00,530 This particular combination of solutions called QServ, 40 00:03:00,530 --> 00:03:04,569 this is an open source implementation that's come out of the LSST Project. 41 00:03:05,620 --> 00:03:09,980 This uses MySQL as the fundamental data store at the backend. 42 00:03:09,980 --> 00:03:11,890 So it's still relational. 43 00:03:11,890 --> 00:03:18,400 Still makes use of SQL as the query language, Maintains acidity, 44 00:03:18,400 --> 00:03:22,310 and it uses an extra d layer on top. 45 00:03:22,310 --> 00:03:26,930 And doesn't share anything between individual instances of the service which 46 00:03:26,930 --> 00:03:32,100 was supporting this, but this is a scalable relational solution designed for 47 00:03:32,100 --> 00:03:37,540 the petascale data volumes that LSST is [INAUDIBLE]. 48 00:03:37,540 --> 00:03:43,310 So this may be a good solution for you, if you're facing that sort of problem. 49 00:03:44,750 --> 00:03:52,780 Moving away from the relational databases, there's the SciDB project. 50 00:03:52,780 --> 00:03:55,680 This is a column oriented database. 51 00:03:55,680 --> 00:04:00,510 Column oriented databases essentially are a, 52 00:04:00,510 --> 00:04:03,190 a 90 degree rotation of relational database. 53 00:04:03,190 --> 00:04:05,550 It's the column which is the important thing, not the row. 54 00:04:07,110 --> 00:04:12,300 And instead of it using tables as it's first order data type for 55 00:04:12,300 --> 00:04:16,340 storing it's based around the idea of numerical arrays. 56 00:04:16,340 --> 00:04:20,990 So this is a very good database for large amounts of numerical data, 57 00:04:20,990 --> 00:04:23,450 large amounts of scientific data. 58 00:04:23,450 --> 00:04:25,660 It has substantial industry backing. 59 00:04:26,810 --> 00:04:31,750 So I would recommend going to SciDB.org to look at this if you're interested in it. 60 00:04:31,750 --> 00:04:35,510 It also maintains ACID, so it's 61 00:04:35,510 --> 00:04:40,400 broadly the same types of transactions as relational databases we're using. 62 00:04:41,470 --> 00:04:44,330 Moving away from the relational model even further, 63 00:04:44,330 --> 00:04:48,290 in recent years there's been a movement called the NoSQL movement. 64 00:04:48,290 --> 00:04:52,390 And the idea is that they want to reject many of the, 65 00:04:52,390 --> 00:04:57,080 the precepts of relational databases and move to something that is far more 66 00:04:57,080 --> 00:05:03,460 performant essentially a glorified hash table with that particular data structure. 67 00:05:03,460 --> 00:05:07,220 So, these are largely optimized key-value stores. 68 00:05:07,220 --> 00:05:11,390 Where the type of data object that's being stored is a key and the value. 69 00:05:11,390 --> 00:05:13,340 So large lookup tables. 70 00:05:13,340 --> 00:05:18,586 They're not ACID, but they're very good for web scale solutions. 71 00:05:18,586 --> 00:05:25,160 The Hadoop, project, has, has produced a number of these. 72 00:05:25,160 --> 00:05:30,950 And certainly they came out of the Google projects with Mapreduce and 73 00:05:30,950 --> 00:05:33,918 stuff like that as, as supporting technology. 74 00:05:33,918 --> 00:05:41,721 A more recent development is the so called, NewSQL, 75 00:05:41,721 --> 00:05:46,460 movement the idea here is to take the, performance that you 76 00:05:46,460 --> 00:05:51,830 get from NoSQL but you want to have a relational model behind it with SQL. 77 00:05:51,830 --> 00:05:57,030 Because that is what we're used to from relational databases. 78 00:05:57,030 --> 00:05:59,730 And a lot of people have technologies and, 79 00:05:59,730 --> 00:06:04,460 and applications which are built on that type of model. 80 00:06:04,460 --> 00:06:10,970 NewSQL examples are H-Store or, or Google Spanner technology examples. 81 00:06:10,970 --> 00:06:18,000 Another possible example is NuoDB which is a NewSQL 82 00:06:18,000 --> 00:06:24,350 type graph based system but for doing that sort of modeling. 83 00:06:24,350 --> 00:06:28,580 And the NewSQL systems make use of what's called a sharding middle layer, 84 00:06:28,580 --> 00:06:29,790 for performance. 85 00:06:29,790 --> 00:06:34,989 Sharding is a type of partitioning where you have multiple horizontal partitions. 86 00:06:36,210 --> 00:06:38,050 for, from the same schema. 87 00:06:38,050 --> 00:06:41,680 So, the idea is to try and optimize the particular data access. 88 00:06:41,680 --> 00:06:43,550 But making use of horizontal partitioning. 89 00:06:43,550 --> 00:06:45,160 But multiple schema versions. 90 00:06:47,280 --> 00:06:50,450 So those are particular types of data store you might want to look at, 91 00:06:50,450 --> 00:06:53,760 if you've got scalability issues with your particular big data problem. 92 00:06:55,440 --> 00:07:00,050 In terms of alternatives to relational data stores that are more based on 93 00:07:00,050 --> 00:07:02,870 some of the other data models we talked about, 94 00:07:02,870 --> 00:07:05,800 it may be that you're actually working with a large amount of XML data. 95 00:07:07,850 --> 00:07:12,730 XML is the W3 standard, the World Wide Consortium 96 00:07:14,790 --> 00:07:17,900 standard for markup language for structure data. 97 00:07:19,500 --> 00:07:22,850 If you've ever seen a data file which has got angle brackets in it. 98 00:07:22,850 --> 00:07:24,880 That's probably an XML file. 99 00:07:24,880 --> 00:07:27,120 It employs a hierarchical data model. 100 00:07:27,120 --> 00:07:28,770 That's the, the type of tree structure. 101 00:07:28,770 --> 00:07:30,510 Do you remember? 102 00:07:30,510 --> 00:07:35,110 And there were a number of supporting technologies for working with XML data. 103 00:07:35,110 --> 00:07:41,370 Xpath is a standard for, for allowing you to point at particular elements or, or 104 00:07:41,370 --> 00:07:48,620 attributes or the values of those, within an XML document, within an XML file. 105 00:07:48,620 --> 00:07:51,830 Xslt so called style sheets. 106 00:07:51,830 --> 00:07:52,740 Standard for 107 00:07:52,740 --> 00:07:58,210 converting that XML angular format to other formats, whether it's HTML, 108 00:07:58,210 --> 00:08:02,700 or comma separated variable, or some other alternate data structure that you want. 109 00:08:03,740 --> 00:08:08,780 And xquery, is a standard for querying XML documents in 110 00:08:08,780 --> 00:08:13,090 the same way that SQL is a standard for querying relational tables. 111 00:08:14,240 --> 00:08:20,120 There are a number of databases which are specifically designed for 112 00:08:20,120 --> 00:08:22,200 working solely with XML data. 113 00:08:22,200 --> 00:08:25,190 These are the so called native XML databases. 114 00:08:25,190 --> 00:08:28,630 eXist is the most popular open source one. 115 00:08:28,630 --> 00:08:33,390 There are also a number of relational databases which have XML support. 116 00:08:33,390 --> 00:08:37,625 MySQL and SQL Server, for example, both have added support for 117 00:08:37,625 --> 00:08:41,160 XML-specific data types in their most recent versions. 118 00:08:44,370 --> 00:08:47,480 An example of an XML, ML document is given here. 119 00:08:47,480 --> 00:08:52,990 You can see that the, the indentation is denoting particular hierarchical levels. 120 00:08:52,990 --> 00:08:55,810 We have element names with inside the angle brackets. 121 00:08:55,810 --> 00:08:59,190 We also have attributes inside the angle brackets. 122 00:08:59,190 --> 00:09:04,740 This is a, a record describing a catalog an astronomical 123 00:09:04,740 --> 00:09:08,950 catalog that may exist in a, in a directory service somewhere. 124 00:09:08,950 --> 00:09:12,710 And you may have multiple examples of, of such things, and 125 00:09:12,710 --> 00:09:15,600 you actually want to work with them in that format. 126 00:09:15,600 --> 00:09:19,320 It may be because of the hierarchy that there's not an actual translation into 127 00:09:19,320 --> 00:09:23,560 the requisite relational data structures in a relational database, 128 00:09:23,560 --> 00:09:26,230 that we would require to represent this type of information, so 129 00:09:26,230 --> 00:09:28,680 using a native XML database may be more performant. 130 00:09:29,820 --> 00:09:33,160 This is an example of XQuery. 131 00:09:33,160 --> 00:09:36,590 This is an equivalent to the select statement. 132 00:09:36,590 --> 00:09:41,870 This is in XQuery, this is called flower, this type of construction. 133 00:09:41,870 --> 00:09:46,170 And you can see that you're defining some namespaces, 134 00:09:46,170 --> 00:09:50,640 they're just defining areas of, concern. 135 00:09:50,640 --> 00:09:54,630 And then you're defining particular variables to be the result of 136 00:09:54,630 --> 00:09:59,530 fx path statements, pointing to particular elements inside an XML document. 137 00:09:59,530 --> 00:10:03,200 And then there's a four loop with a where statement in it. 138 00:10:03,200 --> 00:10:06,980 The predicate argument of that where statement, is exactly the same type of 139 00:10:06,980 --> 00:10:09,810 idea as the predicate that you have in a SQL statement. 140 00:10:09,810 --> 00:10:13,830 There's an ordering and then you can return the result and one of the powers of 141 00:10:13,830 --> 00:10:16,930 XQuery is that you can actually format the result that's returned, 142 00:10:16,930 --> 00:10:21,620 in this case it would format the result in, in a particular type of XML document, 143 00:10:21,620 --> 00:10:24,420 a new XML structure that we would be interested in working with. 144 00:10:25,800 --> 00:10:27,510 This is an example of, of XQuery. 145 00:10:30,000 --> 00:10:34,270 Another way of working with data is in form of RDF, 146 00:10:34,270 --> 00:10:39,105 resource description framework, that I mentioned in the first module. 147 00:10:39,105 --> 00:10:44,760 This is a W3C standard for data interchange. 148 00:10:44,760 --> 00:10:47,540 It makes use of the associative data model. 149 00:10:47,540 --> 00:10:49,530 And if you've heard of the Semantic Web, 150 00:10:49,530 --> 00:10:52,340 it's the main underpinning of the Semantic Web, 151 00:10:52,340 --> 00:10:56,910 the so-called Web of Data, next generation Internet, Web 2.0, that sort of thing. 152 00:10:58,560 --> 00:11:01,865 The basic idea is that you represent all data in the form of 153 00:11:01,865 --> 00:11:04,139 subject-predicate-object triplets. 154 00:11:05,370 --> 00:11:08,880 For example, Pluto is a dwarf planet. 155 00:11:10,570 --> 00:11:13,595 Again there are a number of supporting technologies. 156 00:11:13,595 --> 00:11:19,710 SPARQL, is the, query language for RDF data. 157 00:11:19,710 --> 00:11:24,190 RDFS is a standard for modeling RDFF, RDF data, in, 158 00:11:24,190 --> 00:11:30,900 in the same way that you have database schemas for, for modeling relational data. 159 00:11:30,900 --> 00:11:34,930 SKOS is a standard for representing controlled vocabularies. 160 00:11:36,560 --> 00:11:41,070 this, this gets more into the, the idea of knowledge management, the, 161 00:11:41,070 --> 00:11:45,360 the upper echelons of the Data/Information/Knowledge/Wisdom pyramid. 162 00:11:46,880 --> 00:11:49,790 so, if you have a controlled vocabulary that you're using in 163 00:11:49,790 --> 00:11:53,650 your particular domain, SKOS allows you to represent that in 164 00:11:53,650 --> 00:11:58,690 a formal machine-processable format that you can then program against. 165 00:11:58,690 --> 00:12:05,078 And, ultimately, OWL is for representing concept schemes, so-called ontologies. 166 00:12:05,078 --> 00:12:08,930 Which represent full formal 167 00:12:08,930 --> 00:12:13,380 encapsulizations of domain knowledge in a particular area. 168 00:12:14,790 --> 00:12:19,840 Triple stores are the names given to databases for storing RDF data. 169 00:12:21,740 --> 00:12:25,670 You also find that there are some relational databases out there, 170 00:12:25,670 --> 00:12:30,500 which have SPARQL interfaces, so that they can participate in 171 00:12:32,160 --> 00:12:36,580 distributed queries over RDF data. 172 00:12:36,580 --> 00:12:41,050 This is the so called linked data, or open data, open link data. 173 00:12:42,280 --> 00:12:48,970 That a lot of, big data sources like DBpedia, which is a version of Wikipedia, 174 00:12:48,970 --> 00:12:52,530 have interfaces exposing their data through SPARQL. 175 00:12:52,530 --> 00:12:56,560 And so you can issue queries against those. 176 00:12:56,560 --> 00:12:58,380 This is an example of SPARQL. 177 00:12:58,380 --> 00:13:03,490 The, the top part of the slide shows again you're defining a prefix and 178 00:13:03,490 --> 00:13:06,720 then you're selecting using a select statement. 179 00:13:06,720 --> 00:13:09,720 In SPARQL, you have the question mark to notice the variables that 180 00:13:09,720 --> 00:13:10,260 you're interested in. 181 00:13:10,260 --> 00:13:14,050 And so you're just selecting your, the capital and the country where 182 00:13:16,120 --> 00:13:20,700 what we're asking for here is to find the capitals of countries which are in Europe. 183 00:13:20,700 --> 00:13:24,540 And we would be hitting a triple store containing a set of triples. 184 00:13:24,540 --> 00:13:25,670 Sample data in the, 185 00:13:25,670 --> 00:13:29,590 the lower half of the slide shows the sort of triples that we would have. 186 00:13:29,590 --> 00:13:32,460 So you can see that the where statement in 187 00:13:32,460 --> 00:13:36,590 the SPARQL example is a join of a number of types of statement. 188 00:13:36,590 --> 00:13:39,200 We're looking for cities which have a name. 189 00:13:39,200 --> 00:13:43,340 The city is designated as the capital of a country which is in, 190 00:13:43,340 --> 00:13:46,210 in the continent of Europe in this particular case. 191 00:13:47,730 --> 00:13:51,090 We have a schema which defines a set of predicates. 192 00:13:51,090 --> 00:13:52,110 The predicates, in this case, 193 00:13:52,110 --> 00:13:55,960 would be cityname is capital of countryname isInContinent. 194 00:13:58,450 --> 00:14:02,780 So our sample data, this example would return the first one, 195 00:14:02,780 --> 00:14:05,060 that Berlin is the capital of Germany. 196 00:14:05,060 --> 00:14:07,550 The answer we would get back is Berlin, Germany. 197 00:14:08,630 --> 00:14:13,390 But the other data that is in the database would be required to check the types of 198 00:14:13,390 --> 00:14:15,120 queries that we're interested in making. 199 00:14:16,190 --> 00:14:19,930 Finally I'd like to talk about ontology driven databases. 200 00:14:19,930 --> 00:14:24,810 This is where data is modeled at both the syntactic and the conceptual level. 201 00:14:24,810 --> 00:14:28,130 So this is making use of RDF and owl. 202 00:14:28,130 --> 00:14:33,490 You have your data represented in RDF and it is structured but you also representing 203 00:14:33,490 --> 00:14:38,304 the main knowledge behind the data [SOUND] In terms of the ontology, capturing the, 204 00:14:38,304 --> 00:14:43,149 the concepts and the relationships between those concepts and their properties. 205 00:14:43,149 --> 00:14:48,160 And making use of that in a formal way. 206 00:14:48,160 --> 00:14:55,451 And, an ontology-driven database employs this formally-expressed domain knowledge. 207 00:14:55,451 --> 00:15:01,380 And it allows you to do consistency checks not only against syntactic consistencies, 208 00:15:01,380 --> 00:15:05,570 to make sure that data is in the right data type association with 209 00:15:05,570 --> 00:15:10,850 the right data structures but also for conceptual checks. 210 00:15:10,850 --> 00:15:14,550 That particular definitions of objects and 211 00:15:14,550 --> 00:15:19,096 the source of properties that they may want to have are not inconsistent. 212 00:15:19,096 --> 00:15:22,430 So an example is you may have an ontology, which is describing something like 213 00:15:22,430 --> 00:15:26,570 a star and it may say that a star has a certain set of properties. 214 00:15:26,570 --> 00:15:30,970 It may be that data in your, your database, in your triple store, 215 00:15:30,970 --> 00:15:35,100 is defining a star to have may have, there may be a particular style which has 216 00:15:35,100 --> 00:15:37,790 got a measured property which is inconsistent with that. 217 00:15:37,790 --> 00:15:41,140 And by doing a consistency check against the domain knowledge, 218 00:15:41,140 --> 00:15:45,280 you would be able to identify that inconsistency in your database. 219 00:15:46,940 --> 00:15:51,220 This use of domain knowledge also allows you to do logical inferencing, so 220 00:15:51,220 --> 00:15:53,630 that you can do first order. 221 00:15:53,630 --> 00:15:58,140 Logic, apply first order of logic to your database and 222 00:15:58,140 --> 00:16:03,130 identify new pieces of information which are, are logically consistent or the, 223 00:16:03,130 --> 00:16:11,905 the logical follow on from the, the facts that you have in your database. 224 00:16:11,905 --> 00:16:17,080 So, you may be able to identify new properties based on a set number 225 00:16:17,080 --> 00:16:21,190 of specified properties which are all consistent within the domain knowledge. 226 00:16:21,190 --> 00:16:26,720 Examples of this sort of thing are where you have possibly a database of protein 227 00:16:26,720 --> 00:16:31,390 information and you have an ontology which is describing particular associations 228 00:16:31,390 --> 00:16:36,740 between metabolic phenomena and protein phenomena and 229 00:16:36,740 --> 00:16:42,070 you can infer, particular metabolic phenomena from the protein 230 00:16:42,070 --> 00:16:47,820 phenomena that are specified in the ontology and in your database. 231 00:16:47,820 --> 00:16:50,200 You do not have that information you've specified, but 232 00:16:50,200 --> 00:16:53,770 you can infer it to generate these new facts which are consistent with 233 00:16:53,770 --> 00:16:55,730 the knowledge base that you are using. 234 00:16:55,730 --> 00:17:02,410 This sort of ontology driven database facilitates smart applications. 235 00:17:02,410 --> 00:17:07,080 That means that I can write a, a query against the database, which would 236 00:17:07,080 --> 00:17:11,880 then infer information from the database, consistent with the knowledge base that 237 00:17:11,880 --> 00:17:16,280 I'm using, and give me answers back that I'm interested in. 238 00:17:16,280 --> 00:17:23,470 An example is that I may have a database of zebra fish anatomical 239 00:17:23,470 --> 00:17:29,560 images and their label atop with the particular thing that they are displaying. 240 00:17:29,560 --> 00:17:36,630 But by using an automony, and anatomy infrastructure an anatomy ontolog, 241 00:17:36,630 --> 00:17:42,240 I could infer what substructures of those anatomical structures are, 242 00:17:42,240 --> 00:17:46,990 or, or superstructures are and so I might ask for 243 00:17:46,990 --> 00:17:50,270 sample all images which display the hindbrain, but then it 244 00:17:50,270 --> 00:17:54,850 may also return to me in my search, things related to the central nervous system. 245 00:17:54,850 --> 00:17:56,450 Or to the brain in itself. 246 00:17:56,450 --> 00:17:58,660 Or to individual parts of the hindbrain. 247 00:17:58,660 --> 00:18:00,120 Or possibly to structures or 248 00:18:00,120 --> 00:18:04,930 cell structures that develop into the hind brain, making use of the ontology to 249 00:18:04,930 --> 00:18:07,910 do the logical referencing of that type of information. 250 00:18:07,910 --> 00:18:12,130 Even though that's not necessarily specified in the database itself. 251 00:18:12,130 --> 00:18:17,760 Because it's in the the ontology, we can infer it on the data in the database for 252 00:18:17,760 --> 00:18:18,620 the sake of applications. 253 00:18:20,390 --> 00:18:24,600 So with that I will end this series of six modules on databases. 254 00:18:24,600 --> 00:18:26,130 I hope you find it useful, thank you.