Scott Siu!!! Postgres! 2016!! EnterpriseDB! RedHat RHCA! Who am I! (WeChat)!

Size: px

Start display at page:

Download "Scott Siu!!! Postgres! 2016!! EnterpriseDB! RedHat RHCA! Who am I! (WeChat)!"

穹狄
7 years ago
Views:

1 NOSQL in Postgres! play with MongoDB!

2 Scott Siu!!! Postgres! 2016!! EnterpriseDB! RedHat RHCA! Who am I! (WeChat)!

3 提纲 JSON 的背景要 NoSQL 也要 ACID Postgres: JSON in SQL Play with MongoDB

4 JSON 的背景 JSON(JavaScript Object Notation) 是一种轻量级的数据交换格式易于人阅读和编写同时也易于机器解析和生成它基于 JavaScript Programming Language, Standard ECMA-262 3rd Edition - December 1999 的一个子集 JSON 采用完全独立于语言的文本格式, 但是也使用了类似于 C 语言家族的习惯 ( 包括 C, C++, C#, Java, JavaScript, Perl, Python 等 ) 这些特性使 JSON 成为理想的数据交换语言 JSON 已经是 JavaScript 标准的一部分目前, 主流的浏览器对 JSON 支持都非常完善应用 JSON, 我们可以从 XML 的解析中摆脱出来, 对那些应用 Ajax 的 Web 2.0 网站来说,JSON 确实是目前最灵活的轻量级方案

5 JSON 的背景 <book>! XML! <type>textbook</type>! <pages>256</pages>! <title>programming Pearls 2nd Edition</title>! <description>the first edition of Programming Pearls was one of the most influential books I read early in my career...</ description>! <rating>4.5</rating>! <covertype>paperback</covertype>! <genre>computer Science</genre>! <author>jon Bentley</author>! <publisher>addison-wesley Professional</publisher>! <copyright>1999</copyright>! </book>!

6 JSON 的背景 {! JSON! "book": {! "type": "textbook",! "pages": "256",! "title": "Programming Pearls 2nd Edition",! "description": "The first edition of Programming Pearls was one of the most influential books I read early in my career...",! "rating": "4.5",! "covertype": "paperback",! "genre": "Computer Science",! "author": "Jon Bentley",! "publisher": "Addison-Wesley Professional",! "copyright": "1999"! }! }!

7 JSON 的背景使用上面的 XML 和 JSON 文件分别运行解析测试 10,000,000 次结果并不令人惊讶, 解析和转换 JSON 成一个 Java 对象的速度比 XML 解析速度提高了 30%, 占用空间少 30% 这些结果似乎和多数开发社区对两种格式的看法一样所以, 换用 JSON 处理数据在性能上可以有不小的提升, 而且还会减少空间的占用

8 要 NoSQL 与要 ACID 传统用户数据场景 RDBMS - 关系型数据库应用系统格式各异的同类数据格式化统一的数据模型

9 要 NoSQL 与要 ACID 灵活存储各种格式应用定义数据类型大表, 大字段可拆可合移动互联网新数据场景 NoSQL 非关系型数据库

10 要 NoSQL 与要 ACID 传统用户数据场景强一致性分段进行规范统一移动互联网数据场景灵活构建整体存储分段展现左边 Insert, 右边 save 左边 Select, 右边 find 码农就是 996 终生无缘 007

11 Postgres: JSON in SQL HSTORE Key-value pair Simple, fast and easy Postgres v 8.2 pre-dates many NoSQL-only solutions Ideal for flat data structures that are sparsely populated JSON Hierarchical document model Introduced in Postgres 9.2, perfected in 9.3 JSONB Binary version of JSON Faster, more operators and even more robust Postgres 9.4

12 Postgres: JSON in SQL Creating a table with a JSONB field CREATE TABLE json_data (data JSONB); Simple JSON data element: {"name": "Apple Phone", "type": "phone", "brand":"acme", "price": 200, "available": true, "warranty_years": 1} Inserting this data element into the table json_data INSERT INTO json_data (data) VALUES (' {"name": "Apple Phone", "type": "phone", "brand": "ACME", "price": 200, "available": true, "warranty_years": 1} ')

13 Postgres: JSON in SQL 支持嵌套数组 : { full name : John Joseph Carl Salinger, names : [ {"type": "firstname", value : John }, { type : middlename, value : Joseph }, { type : middlename, value : Carl }, { type : lastname, value : Salinger } ] }

14 Postgres: JSON in SQL SELECT DISTINCT data->>'name' as products FROM json_data; products Cable TV Basic Service Package AC3 Case Black Phone Service Basic Plan AC3 Phone AC3 Case Green Phone Service Family Plan 注意 : 这里返回的不是 JSON 格式的数据, 而是返回普通的字符串类型数据, 传统应用可以直接读取进行展示

15 Postgres: JSON in SQL SELECT data FROM json_data; data {"name": "Apple Phone", "type": "phone", "brand": "ACME", "price": 200,"available": true, "warranty_years": 1} 注意 : 这里返回的是 JSON 格式的字符串类型, 可以直接通过如 Node.js 等进行直接读取, 解析并展示到页面上

16 Postgres: JSON in SQL 1. Number: Signed decimal number that may contain a fractional part and may use exponential notation. No distinction between integer and floating-point 2. String A sequence of zero or more Unicode characters. Strings are delimited with double-quotation mark Supports a backslash escaping syntax. 3. Boolean Either of the values true or false.

17 Postgres: JSON in SQL 4. Array An ordered list of zero or more values, Each values may be of any type. Arrays use square bracket notation with elements being comma-separated. 5. Object An unordered associative array (name/value pairs). Objects are delimited with curly brackets Commas to separate each pair Each pair the colon ':' character separates the key or name from its value. All keys must be strings and should be distinct from each other within that object. 6. null An empty value, using the word null

18 Postgres: JSON in SQL { "firstname": "John", -- String Type "lastname": "Smith", -- String Type "isalive": true, -- Boolean Type "age": 25, -- Number Type "height_cm": 167.6, -- Number Type "address": { -- Object Type "streetaddress": "21 2nd Street, "city": "New York, "state": "NY, "postalcode": " }, "phonenumbers": [ // Object Array { "type": "home, "number": " }, // Object { "type": "office, "number": " } ], "children": [], "spouse": null }

$INTO json_data VALUES ('{"name":"xxx","brand":"aaa",$

19 Postgres: JSON in SQL CREATE TABLE json_data (data jsonb); INSERT INTO json_data VALUES ('{"name":"xxx","brand":"aaa", "available":true}');

20 Postgres: JSON in SQL JSONB 数据类型 Canonical representation Whitespace and punctuation dissolved away Only one value per object key is kept Last insert wins Key order determined by length, then bytewise comparison Equality, containment and key/element presence tests Smaller, faster GIN indexes jsonb subdocument indexes Use get operators to construct expression indexes on subdocument: CREATE INDEX author_index ON books USING GIN ((jsondata -> 'authors')); SELECT * FROM books WHERE jsondata -> 'authors'? 'Carl Bernstein'

21 Postgres: JSON in SQL

22 Play with MongoDB

23 Play with MongoDB CREATE EXTENSION mongo_fdw; CREATE SERVER mongo_server FOREIGN DATA WRAPPER mongo_fdw OPTIONS (address ' ', port '27017'); CREATE USER MAPPING FOR postgresql SERVER mongo_server OPTIONS (username 'mongo', password 'mongo'); CREATE FOREIGN TABLE mongo_data( name text, brand text, type text) SERVER mongo_server OPTIONS ( database 'benchmark', collection 'json_tables');

24 Play with MongoDB SELECT * FROM mongo_data WHERE brand='acme' limit 10; name brand type AC7553 Phone ACME phone AC7551 Phone ACME phone AC7519 Phone ACME phone AC7565 Phone ACME phone AC7555 Phone ACME phone AC7529 Phone ACME phone AC7528 Phone ACME phone AC7547 Phone ACME phone AC7587 Phone ACME phone AC7541 Phone ACME phone (10 rows)

25 Play with MongoDB INSERT INTO mongo_data(name, brand, type) VALUES('iphone6 phone','apple Inc','phone'); SELECT* FROM mongo_data WHERE brand='apple Inc'; _id name brand type ea4f59fe5586a d iphone6 phone Apple Inc phone UPDATE mongo_data SET brand='apple Product' WHERE brand='apple Inc ; SELECT * FROM mongo_data WHERE brand='apple Product ; _id name brand type ea4f59fe5586a d iphone6 phone Apple Product phone

26 PostgreSQL! - Not Only SQL(NOSQL) -!

27 (WeChat)!! Q & A!

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘

學科 100% ( 為單複選題, 每題 2.5 分, 共 100 分 ) 1. 請參閱附圖作答 : (A) 選項 A (B) 選項 B (C) 選項 C (D) 選項 D Ans:D 2. 下列對於資料庫正規化 (Normalization) 的敘 ITE 資訊專業人員鑑定資料庫系統開發與設計實務試卷編號 :IDS101 注意事項一本測驗為單面印刷試題, 共計十三頁第二至十三頁為四十道學科試題, 測驗時間 90 分鐘 : 每題 2.5 分, 總測驗時間為 90 分鐘二執行 CSF 測驗系統 -Client