çãããããã«ã¡ã¯ã XNUMX æã®äŒæã®åŸåã«åºçºããåã«ãã³ãŒã¹ã§ã®æ°ããã¹ããªãŒã ã®éå§ã«åããŠç¿»èš³ããè³æãå
±æããŸãã
ã¢ããªã±ãŒã·ã§ã³éçºè
ã¯ãè€æ°ã®éçšããŒã¿ããŒã¹ãæ¯èŒããŠãç®çã®ã¯ãŒã¯ããŒãã«æãé©ããããŒã¿ããŒã¹ãéžæããããšã«å€ãã®æéãè²»ãããŸãã ããŒãºã«ã¯ãç°¡ç¥åãããããŒã¿ ã¢ããªã³ã°ããã©ã³ã¶ã¯ã·ã§ã³ä¿èšŒãèªã¿åã/æžã蟌ã¿ããã©ãŒãã³ã¹ãæ°Žå¹³ã¹ã±ãŒãªã³ã°ãããã³ãã©ãŒã«ã ãã¬ã©ã³ã¹ãå«ãŸããå ŽåããããŸãã åŸæ¥ãéžæã¯ããŒã¿ããŒã¹ ã«ããŽãª (SQL ã NoSQL) ããå§ãŸããŸããããã¯ãåã«ããŽãªã«ã¯æ確ãªãã¬ãŒããªããããããã§ãã äœã¬ã€ãã³ã·ãŒãšé«ã¹ã«ãŒãããã®èŠ³ç¹ããã®é«ãããã©ãŒãã³ã¹ã¯ãäžè¬ã«éãã¬ãŒããªãã®èŠä»¶ãšã¿ãªãããŠããããããããããµã³ãã« ããŒã¿ããŒã¹ã«ãšã£ãŠäžå¯æ¬ ã§ãã
ãã®èšäºã®ç®çã¯ãã¢ããªã±ãŒã·ã§ã³éçºè ãã¢ããªã±ãŒã·ã§ã³ ããŒã¿ ã¢ããªã³ã°ã®ã³ã³ããã¹ã㧠SQL ãš NoSQL ã®ã©ã¡ããé©åã«éžæã§ããããæ¯æŽããããšã§ãã XNUMX ã€ã® SQL ããŒã¿ããŒã¹ (PostgreSQL) ãš XNUMX ã€ã® NoSQL ããŒã¿ããŒã¹ (Cassandra ãš MongoDB) ãèŠãŠãããŒãã«ã®äœæãããŒãã«ãžã®ããŒã¿ã®å ¥åãããŒãã«ããã®ããŒã¿ã®èªã¿åããããŒãã«ã®åé€ãªã©ã®ããŒã¿ããŒã¹èšèšã®åºæ¬ã説æããŸãã 次åã®èšäºã§ã¯ãã€ã³ããã¯ã¹ããã©ã³ã¶ã¯ã·ã§ã³ãJOINãTTL ãã£ã¬ã¯ãã£ããããã³ JSON ããŒã¹ã®ããŒã¿ããŒã¹èšèšã«ã€ããŠèŠãŠãããŸãã
SQLãšNoSQLã®éãã¯äœã§ãã?
SQL ããŒã¿ããŒã¹ã¯ãACID ãã©ã³ã¶ã¯ã·ã§ã³ä¿èšŒãšãæ¢åã®æ£èŠåããããªã¬ãŒã·ã§ãã« ããŒã¿ããŒã¹ ã¢ãã«äžã§äºæããªãæ¹æ³ã§ JOIN ã䜿çšããŠããŒã¿ãã¯ãšãªããæ©èœã«ãã£ãŠãã¢ããªã±ãŒã·ã§ã³ã®æè»æ§ãé«ããŸãã
ã¢ããªã·ãã¯/åäžããŒã ã¢ãŒããã¯ãã£ãšåé·æ§ã®ããã®ãã¹ã¿ãŒ/ã¹ã¬ãŒã ã¬ããªã±ãŒã·ã§ã³ ã¢ãã«ã®äœ¿çšãèæ ®ãããšãåŸæ¥ã® SQL ããŒã¿ããŒã¹ã«ã¯ XNUMX ã€ã®éèŠãªæ©èœãã€ãŸãç·åœ¢æžã蟌ã¿ã¹ã±ãŒã©ããªã㣠(ã€ãŸããè€æ°ã®ããŒãã«ãããèªåããŒãã£ã·ã§ãã³ã°) ãšèªå/ãŒã ããŒã¿æ倱ãæ¬ ããŠããŸãã ããã¯ãåä¿¡ããããŒã¿ã®éãåäžããŒãã®æ倧æžã蟌ã¿ã¹ã«ãŒããããè¶ ããããšãã§ããªãããšãæå³ããŸãã ããã«ããã©ãŒã«ã ãã¬ã©ã³ã¹ (ã·ã§ã¢ãŒãããã·ã³ã° ã¢ãŒããã¯ãã£ã«ããã) ã§ã¯ãäžæçãªããŒã¿æ倱ã®äžéšãèæ ®ããå¿ èŠããããŸãã ããã§ãæè¿ã®ã³ãããããŸã ã¹ã¬ãŒã ã³ããŒã«åæ ãããŠããªãããšã«çæããå¿ èŠããããŸãã SQL ããŒã¿ããŒã¹ã§ã¯ãããŠã³ã¿ã€ã ãªãã§æŽæ°ãå®çŸããããšãå°é£ã§ãã
NoSQL ããŒã¿ããŒã¹ã¯éåžžãæ¬è³ªçã«åæ£ãããŠããŸãã ãããã§ã¯ãããŒã¿ãã»ã¯ã·ã§ã³ã«åå²ãããè€æ°ã®ããŒãã«åæ£ãããŸãã éæ£èŠåãå¿ èŠã§ãã ããã¯ãéä¿¡ããç¹å®ã®ãªã¯ãšã¹ãã«å¿çããããã«ãå ¥åãããããŒã¿ãäœåºŠãã³ããŒããå¿ èŠãããããšãæå³ããŸãã å šäœçãªç®æšã¯ãèªã¿åãäžã«äœ¿çšå¯èœãªã·ã£ãŒãã®æ°ãæžããããšã§é«ãããã©ãŒãã³ã¹ãå®çŸããããšã§ãã ããã¯ãNoSQL ã§ã¯ã¯ãšãªãã¢ãã«åããå¿ èŠãããã®ã«å¯ŸããSQL ã§ã¯ããŒã¿ãã¢ãã«åããå¿ èŠãããããšãæå³ããŸãã
NoSQL ã¯ãåæ£ã¯ã©ã¹ã¿ãŒã§é«ãããã©ãŒãã³ã¹ãéæããããšã«éç¹ã眮ããŠãããããããACID ãã©ã³ã¶ã¯ã·ã§ã³æ倱ãJOINãäžè²«æ§ã®ããã°ããŒãã« ã»ã«ã³ã㪠ã€ã³ããã¯ã¹ãå«ãå€ãã®ããŒã¿ããŒã¹èšèšã®ãã¬ãŒããªãã®åºç€ãšãªãçè«çæ ¹æ ã§ãã
NoSQL ããŒã¿ããŒã¹ã¯ç·åœ¢æžã蟌ã¿ã¹ã±ãŒã©ããªãã£ãšé«ããã©ãŒã«ã ãã¬ã©ã³ã¹ãæäŸããŸããããã©ã³ã¶ã¯ã·ã§ã³ä¿èšŒã倱ãããããããã·ã§ã³ ã¯ãªãã£ã«ã«ãªããŒã¿ã«ã¯é©ããªããšããè°è«ããããŸãã
次ã®è¡šã¯ãNoSQL ã®ããŒã¿ ã¢ããªã³ã°ã SQL ãšã©ã®ããã«ç°ãªããã瀺ããŠããŸãã
SQL ãš NoSQL: ãªãäž¡æ¹ãå¿
èŠãªã®ã§ãããã?
Amazon.comãNetflixãUberãAirbnb ãªã©ãå€æ°ã®ãŠãŒã¶ãŒãæ±ããçŸå®ã®ã¢ããªã±ãŒã·ã§ã³ã¯ãè€éã§å€é¢çãªã¿ã¹ã¯ãå®è¡ããå¿ èŠããããŸãã ããšãã°ãAmazon.com ã®ãããªé»ååååŒã¢ããªã±ãŒã·ã§ã³ã§ã¯ããŠãŒã¶ãŒæ å ±ã補åã泚æãè«æ±æžãªã©ã®è»œéã§éèŠåºŠã®é«ãããŒã¿ãšã補åã¬ãã¥ãŒããµããŒã ã¡ãã»ãŒãžããŠãŒã¶ãŒ ã¢ã¯ãã£ããã£ãªã©ã®éããŠæ©å¯æ§ã®äœãããŒã¿ãä¿åããå¿ èŠããããŸãããŠãŒã¶ãŒã®ã¬ãã¥ãŒãšæšå¥šäºé ã åœç¶ã®ããšãªããããããã®ã¢ããªã±ãŒã·ã§ã³ã¯å°ãªããšã XNUMX ã€ã® SQL ããŒã¿ããŒã¹ãšå°ãªããšã XNUMX ã€ã® NoSQL ããŒã¿ããŒã¹ã«äŸåããŸãã ãªãŒãžã§ã³ãè¶ããã°ããŒãã« ã·ã¹ãã ã§ã¯ãNoSQL ããŒã¿ããŒã¹ã¯ãç¹å®ã®ãªãŒãžã§ã³ã§å®è¡ãããŠããä¿¡é Œã§ãããœãŒã¹ SQL ããŒã¿ããŒã¹ã«ä¿åãããŠããããŒã¿ã®å°çåæ£ãã£ãã·ã¥ãšããŠåäœããŸãã
YugaByte DB 㯠SQL ãš NoSQL ãã©ã®ããã«çµã¿åãããŠããŸãã?
YugaByte DB ã¯ããã°æåã®æ··åã¹ãã¬ãŒãž ãšã³ãžã³ãèªåã·ã£ãŒãã£ã³ã°ãã·ã£ãŒãã£ã³ã°ãããåæ£ã³ã³ã»ã³ãµã¹ ã¬ããªã±ãŒã·ã§ã³ãããã³ ACID åæ£ãã©ã³ã¶ã¯ã·ã§ã³ (Google Spanner ããã€ã³ã¹ãã¬ãŒã·ã§ã³ãåãã) äžã«æ§ç¯ãããŠãããNoSQL (Cassandra ããã³ Redis ) ãšåæã«äºææ§ãããäžçåã®ãªãŒãã³ ãœãŒã¹ ããŒã¿ããŒã¹ã§ãã SQL (PostgreSQL)ã 以äžã®è¡šã«ç€ºãããã«ãCassandra ãšäºææ§ã®ãã YugaByte DB API ã§ãã YCQL ã¯ãåäžããŒããã³ãã«ãã㌠ACID ãã©ã³ã¶ã¯ã·ã§ã³ãšã°ããŒãã« ã»ã«ã³ã㪠ã€ã³ããã¯ã¹ã®æŠå¿µã NoSQL API ã«è¿œå ããããã«ãã£ãŠãã©ã³ã¶ã¯ã·ã§ã³ NoSQL ããŒã¿ããŒã¹ã®æ代ãè¿ããŸãã ããã«ãPostgreSQL ãšäºææ§ã®ãã YugaByte DB API ã§ãã YCQL ã¯ãSQL API ã«ç·åœ¢æžã蟌ã¿ã¹ã±ãŒãªã³ã°ãšèªåãã©ãŒã«ã ãã¬ã©ã³ã¹ã®æŠå¿µãè¿œå ããåæ£ SQL ããŒã¿ããŒã¹ãäžçã«ãããããŸãã YugaByte DB ã¯æ¬è³ªçã«ãã©ã³ã¶ã¯ã·ã§ã³ã§ãããããNoSQL API ãããã·ã§ã³ã¯ãªãã£ã«ã«ãªããŒã¿ã®ã³ã³ããã¹ãã§äœ¿çšã§ããããã«ãªããŸããã
以åã®èšäºã§ãè¿°ã¹ãããã«ã
- äž»ãªã¯ãŒã¯ããŒãããã«ãã㌠JOIN æäœã§ããå ŽåãYSQL ãéžæãããšãã¯ãããŒãè€æ°ã®ããŒãã«åæ£ãããå¯èœæ§ãããããšãç解ããŠãã ããããã®çµæãNoSQL ãããåŸ ã¡æéãé·ããªã£ãããã¹ã«ãŒããããäœããªã£ããããŸãã
- ãã以å€ã®å Žåã¯ãäžåºŠã« XNUMX ã€ã®ããŒãããã¯ãšãªãå®è¡ããããšã§ããã©ãŒãã³ã¹ãåäžããããšã«çæããŠãXNUMX ã€ã® NoSQL API ã®ãããããéžæããŠãã ããã YugaByte DB ã¯ãè€æ°ã®ã¯ãŒã¯ããŒããåæã«ç®¡çããå¿ èŠãããçŸå®äžçã®è€éãªã¢ããªã±ãŒã·ã§ã³çšã®åäžã®éçšããŒã¿ããŒã¹ãšããŠæ©èœããŸãã
次ã®ã»ã¯ã·ã§ã³ã®ããŒã¿ ã¢ããªã³ã° ã©ãã¯ããã€ãã£ã ããŒã¿ããŒã¹ã§ã¯ãªããPostgreSQL ããã³ Cassandra API ãšäºææ§ã®ãã YugaByte DB ããŒã¿ããŒã¹ã«åºã¥ããŠããŸãã ãã®ã¢ãããŒãã§ã¯ãXNUMX ã€ã®ç°ãªãããŒã¿ããŒã¹ã®å®å
šã«ç¬ç«ããã¯ã©ã¹ã¿ãŒã䜿çšããã®ã§ã¯ãªããåãããŒã¿ããŒã¹ ã¯ã©ã¹ã¿ãŒã® XNUMX ã€ã®ç°ãªã API (XNUMX ã€ã®ç°ãªãããŒãäž) ãšã®å¯Ÿè©±ã®å®¹æãã匷調ãããŸãã
次ã®ã»ã¯ã·ã§ã³ã§ã¯ãããŒã¿ ã¢ããªã³ã° ã©ããèŠãŠã察象ãšãªãããŒã¿ããŒã¹ã®çžéç¹ãšããã€ãã®å
±éç¹ã説æããŸãã
ããŒã¿ã¢ããªã³ã°ç 究宀
ããŒã¿ããŒã¹ã®ã€ã³ã¹ããŒã«
(è€éãªãããã€ã¡ã³ã ã¢ãŒããã¯ãã£ã§ã¯ãªã) ããŒã¿ ã¢ãã«ã®èšèšã«éç¹ã眮ããŠãããããããŒã«ã« ãã·ã³äžã® Docker ã³ã³ããã«ããŒã¿ããŒã¹ãã€ã³ã¹ããŒã«ããããããã®ã³ãã³ã ã©ã€ã³ ã·ã§ã«ã䜿çšããŠããŒã¿ããŒã¹ãæäœããŸãã
PostgreSQL ããã³ Cassandra ãšäºææ§ã®ãã YugaByte DB ããŒã¿ããŒã¹
mkdir ~/yugabyte && cd ~/yugabyte
wget https://downloads.yugabyte.com/yb-docker-ctl && chmod +x yb-docker-ctl
docker pull yugabytedb/yugabyte
./yb-docker-ctl create --enable_postgres
MongoDBã®
docker run --name my-mongo -d mongo:latest
ã³ãã³ãã©ã€ã³ã¢ã¯ã»ã¹
察å¿ãã API ã®ã³ãã³ã ã©ã€ã³ ã·ã§ã«ã䜿çšããŠããŒã¿ããŒã¹ã«æ¥ç¶ããŸãããã
PostgreSQL
docker exec -it yb-postgres-n1 /home/yugabyte/postgres/bin/psql -p 5433 -U postgres
ã«ãµã³ãã©
cqlsh
ã«ã¿ãã°ã«ãã bin
.
CQL 㯠SQL ããã€ã³ã¹ãã¬ãŒã·ã§ã³ãåããŠãããããŒãã«ãè¡ãåãã€ã³ããã¯ã¹ã®åæ§ã®æŠå¿µãæã£ãŠããããšã«æ³šæããŠãã ããã ãã ããNoSQL èšèªãšããŠäžå®ã®å¶éãè¿œå ãããŠããããã®ã»ãšãã©ã«ã€ããŠã¯ä»ã®èšäºã§ã説æããŸãã
docker exec -it yb-tserver-n1 /home/yugabyte/bin/cqlsh
MongoDBã®
docker exec -it my-mongo bash
cd bin
mongo
ããŒãã«ã®äœæ
ããã§ãã³ãã³ã ã©ã€ã³ã䜿çšããŠããŒã¿ããŒã¹ãšå¯Ÿè©±ããããŸããŸãªæäœãå®è¡ã§ããããã«ãªããŸããã ãŸãã¯ãããŸããŸãªã¢ãŒãã£ã¹ãã«ãã£ãŠæžãããæ²ã«é¢ããæ å ±ãä¿åããããŒãã«ãäœæããŸãããã ãããã®æ²ã¯ã¢ã«ãã ã®äžéšã§ããå ŽåããããŸãã ãŸããæ²ã®ãªãã·ã§ã³ã®å±æ§ãšããŠããªãªãŒã¹å¹ŽãäŸ¡æ Œããžã£ã³ã«ãè©äŸ¡ããããŸãã ãã¿ã°ããã£ãŒã«ããéããŠãå°æ¥å¿ èŠã«ãªãå¯èœæ§ã®ããè¿œå ã®å±æ§ãèæ ®ããå¿ èŠããããŸãã åæ§é åããŒã¿ãããŒãšå€ã®ãã¢ã®åœ¢åŒã§ä¿åã§ããŸãã
PostgreSQL
CREATE TABLE Music (
Artist VARCHAR(20) NOT NULL,
SongTitle VARCHAR(30) NOT NULL,
AlbumTitle VARCHAR(25),
Year INT,
Price FLOAT,
Genre VARCHAR(10),
CriticRating FLOAT,
Tags TEXT,
PRIMARY KEY(Artist, SongTitle)
);
ã«ãµã³ãã©
Cassandra ã§ã®ããŒãã«ã®äœæ㯠PostgreSQL ãšéåžžã«äŒŒãŠããŸãã äž»ãªéãã® XNUMX ã€ã¯ãæŽåæ§å¶çŽ (NOT NULL ãªã©) ããªãããšã§ããããã㯠NoSQL ããŒã¿ããŒã¹ã§ã¯ãªãã¢ããªã±ãŒã·ã§ã³ã®è²¬ä»»ã§ããã äž»ããŒã¯ãããŒãã£ã·ã§ã³ ã㌠(以äžã®äŸã® Artist å) ãšäžé£ã®ã¯ã©ã¹ã¿ãªã³ã°å (以äžã®äŸã® SongTitle å) ã§æ§æãããŸãã ããŒãã£ã·ã§ã³ ããŒã¯è¡ãã©ã®ããŒãã£ã·ã§ã³/ã·ã£ãŒãã«é 眮ãããã決å®ããã¯ã©ã¹ã¿ãªã³ã°åã¯çŸåšã®ã·ã£ãŒãå ã§ããŒã¿ãã©ã®ããã«ç·šæãããã瀺ããŸãã
CREATE KEYSPACE myapp;
USE myapp;
CREATE TABLE Music (
Artist TEXT,
SongTitle TEXT,
AlbumTitle TEXT,
Year INT,
Price FLOAT,
Genre TEXT,
CriticRating FLOAT,
Tags TEXT,
PRIMARY KEY(Artist, SongTitle)
);
MongoDBã®
MongoDB ã¯ãããŒã¿ãããŒã¿ããŒã¹ (ããŒã¿ããŒã¹) (Cassandra ã®ããŒã¹ããŒã¹ã«äŒŒãŠããŸã) ã«ç·šæããŸããããã«ã¯ãããã¥ã¡ã³ã (ããŒãã«ã®è¡ã«äŒŒãŠããŸã) ãå«ãã³ã¬ã¯ã·ã§ã³ (ããŒãã«ã«äŒŒãŠããŸã) ããããŸãã MongoDB ã§ã¯ãåºæ¬çã«åæã¹ããŒããå®çŸ©ããå¿ èŠã¯ãããŸããã ããŒã ãããŒã¿ããŒã¹ã䜿çšããã以äžã«ç€ºãããã«ãæåã®åŒã³åºãã§ããŒã¿ããŒã¹ãã€ã³ã¹ã¿ã³ã¹åããæ°ããäœæãããããŒã¿ããŒã¹ã®ã³ã³ããã¹ããå€æŽããŸãã ã³ã¬ã¯ã·ã§ã³ã§ãã£ãŠããæ瀺çã«äœæããå¿ èŠã¯ãªããæåã®ããã¥ã¡ã³ããæ°ããã³ã¬ã¯ã·ã§ã³ã«è¿œå ããã ãã§èªåçã«äœæãããŸãã MongoDB ã¯ããã©ã«ãã§ãã¹ã ããŒã¿ããŒã¹ã䜿çšãããããç¹å®ã®ããŒã¿ããŒã¹ãæå®ããªãã³ã¬ã¯ã·ã§ã³ ã¬ãã«ã®æäœã¯ããã©ã«ãã§ãã¹ã ããŒã¿ããŒã¹äžã§å®è¡ãããããšã«æ³šæããŠãã ããã
use myNewDatabase;
ããŒãã«ã«é¢ããæ
å ±ã®ååŸ
PostgreSQL
d Music
Table "public.music"
Column | Type | Collation | Nullable | Default
--------------+-----------------------+-----------+----------+--------
artist | character varying(20) | | not null |
songtitle | character varying(30) | | not null |
albumtitle | character varying(25) | | |
year | integer | | |
price | double precision | | |
genre | character varying(10) | | |
criticrating | double precision | | |
tags | text | | |
Indexes:
"music_pkey" PRIMARY KEY, btree (artist, songtitle)
ã«ãµã³ãã©
DESCRIBE TABLE MUSIC;
CREATE TABLE myapp.music (
artist text,
songtitle text,
albumtitle text,
year int,
price float,
genre text,
tags text,
PRIMARY KEY (artist, songtitle)
) WITH CLUSTERING ORDER BY (songtitle ASC)
AND default_time_to_live = 0
AND transactions = {'enabled': 'false'};
MongoDBã®
use myNewDatabase;
show collections;
ããŒãã«ãžã®ããŒã¿ã®å
¥å
PostgreSQL
INSERT INTO Music
(Artist, SongTitle, AlbumTitle,
Year, Price, Genre, CriticRating,
Tags)
VALUES(
'No One You Know', 'Call Me Today', 'Somewhat Famous',
2015, 2.14, 'Country', 7.8,
'{"Composers": ["Smith", "Jones", "Davis"],"LengthInSeconds": 214}'
);
INSERT INTO Music
(Artist, SongTitle, AlbumTitle,
Price, Genre, CriticRating)
VALUES(
'No One You Know', 'My Dog Spot', 'Hey Now',
1.98, 'Country', 8.4
);
INSERT INTO Music
(Artist, SongTitle, AlbumTitle,
Price, Genre)
VALUES(
'The Acme Band', 'Look Out, World', 'The Buck Starts Here',
0.99, 'Rock'
);
INSERT INTO Music
(Artist, SongTitle, AlbumTitle,
Price, Genre,
Tags)
VALUES(
'The Acme Band', 'Still In Love', 'The Buck Starts Here',
2.47, 'Rock',
'{"radioStationsPlaying": ["KHCR", "KBQX", "WTNR", "WJJH"], "tourDates": { "Seattle": "20150625", "Cleveland": "20150630"}, "rotation": Heavy}'
);
ã«ãµã³ãã©
å
šäœã®è¡šçŸ INSERT
Cassandra ã®èŠãç®ã¯ PostgreSQL ã®èŠãç®ãšéåžžã«ãã䌌ãŠããŸãã ãã ããã»ãã³ãã£ã¯ã¹ã«ã¯å€§ããªéãã XNUMX ã€ãããŸãã ã«ãµã³ãã©ã§ INSERT
å®éã«ã¯æäœã§ã UPSERT
ãè¡ããã§ã«ååšããå ŽåãæåŸã®å€ãè¡ã«è¿œå ãããŸãã
ããŒã¿å ¥åã¯PostgreSQLã«äŒŒãŠããŸã
INSERT
äžèš
.
MongoDBã®
MongoDB 㯠Cassandra ãšåã NoSQL ããŒã¿ããŒã¹ã§ããããã®æ¿å
¥æäœã¯ Cassandra ã®ã»ãã³ãã£ãã¯åäœãšäœã®å
±éç¹ããããŸããã MongoDB å
UPSERT
, ãã®ãããPostgreSQL ã«äŒŒãŠããŸãã ããã©ã«ãããŒã¿ãè¿œå ããã« _idspecified
æ°ããããã¥ã¡ã³ããã³ã¬ã¯ã·ã§ã³ã«è¿œå ãããŸãã
db.music.insert( {
artist: "No One You Know",
songTitle: "Call Me Today",
albumTitle: "Somewhat Famous",
year: 2015,
price: 2.14,
genre: "Country",
tags: {
Composers: ["Smith", "Jones", "Davis"],
LengthInSeconds: 214
}
}
);
db.music.insert( {
artist: "No One You Know",
songTitle: "My Dog Spot",
albumTitle: "Hey Now",
price: 1.98,
genre: "Country",
criticRating: 8.4
}
);
db.music.insert( {
artist: "The Acme Band",
songTitle: "Look Out, World",
albumTitle:"The Buck Starts Here",
price: 0.99,
genre: "Rock"
}
);
db.music.insert( {
artist: "The Acme Band",
songTitle: "Still In Love",
albumTitle:"The Buck Starts Here",
price: 2.47,
genre: "Rock",
tags: {
radioStationsPlaying:["KHCR", "KBQX", "WTNR", "WJJH"],
tourDates: {
Seattle: "20150625",
Cleveland: "20150630"
},
rotation: "Heavy"
}
}
);
ããŒãã«ã¯ãšãª
ãããããã¯ãšãªæ§ç¯ã«é¢ã㊠SQL ãš NoSQL ã®æã倧ããªéãã¯ã䜿çšãããèšèªã§ãã FROM
О WHERE
ã SQL ã§ã¯åŒã®åŸã«äœ¿çšã§ããŸã FROM
è€æ°ã®ããŒãã«ãéžæããåŒã䜿çšããŸã WHERE
ä»»æã®è€éã (æäœãå«ã) ã«ããããšãã§ããŸãã JOIN
ããŒãã«éïŒã ãã ããNoSQL ã¯å³ããå¶éã課ãåŸåããããŸãã FROM
ãæå®ããã XNUMX ã€ã®ããŒãã«ã®ã¿ãæäœããŸãã WHERE
ãäž»ããŒã¯åžžã«æå®ããå¿
èŠããããŸãã ããã¯ãå
ã»ã©èª¬æãã NoSQL ã®ããã©ãŒãã³ã¹åäžã«ã€ãªãããŸãã ãã®èŠæã«ãããã¯ãã¹è¡šãã¯ãã¹ããŒã®çžäºäœçšãå¯èœãªéãåæžããããšãã§ããŸãã ãªã¯ãšã¹ãã«å¿çãããšãã«ããŒãééä¿¡ã«å€§ããªé
延ãçããå¯èœæ§ããããããäžè¬çã«ã¯åé¿ããã®ãæåã§ãã ããšãã°ãCassandra ã§ã¯ã¯ãšãªãç¹å®ã®æŒç®å (ã®ã¿) ã«å¶éããå¿
èŠããããŸãã =, IN, <, >, =>, <=
) ã»ã«ã³ã㪠ã€ã³ããã¯ã¹ãèŠæ±ããå Žåãé€ããããŒãã£ã·ã§ã³ ããŒã«å¯ŸããŠäœ¿çšããŸã (ããã§ã¯ = æŒç®åã®ã¿ãèš±å¯ãããŸã)ã
PostgreSQL
以äžã«ãSQL ããŒã¿ããŒã¹ã§ç°¡åã«å®è¡ã§ããã¯ãšãªã® XNUMX ã€ã®äŸã瀺ããŸãã
- ã¢ãŒãã£ã¹ãããšã«ãã¹ãŠã®æ²ã衚瀺ããŸãã
- ã¿ã€ãã«ã®æåã®éšåã«äžèŽããã¢ãŒãã£ã¹ãã®ãã¹ãŠã®æ²ã衚瀺ããŸãã
- ã¿ã€ãã«ã«ç¹å®ã®åèªãå«ãŸããäŸ¡æ Œã 1.00 æªæºã§ããã¢ãŒãã£ã¹ãã®ãã¹ãŠã®æ²ã衚瀺ããŸãã
SELECT * FROM Music
WHERE Artist='No One You Know';
SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle LIKE 'Call%';
SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle LIKE '%Today%'
AND Price > 1.00;
ã«ãµã³ãã©
äžèšã® PostgreSQL ã¯ãšãªã®ãã¡ãCassandra ã§ã¯æåã®ã¯ãšãªã®ã¿ãå€æŽãããã«æ©èœããŸãã LIKE
ãªã©ã®ã¯ã©ã¹ã¿ãªã³ã°åã«ã¯é©çšã§ããŸããã SongTitle
ã ãã®å ŽåãæŒç®åã®ã¿ãèš±å¯ãããŸã =
О IN
.
SELECT * FROM Music
WHERE Artist='No One You Know';
SELECT * FROM Music
WHERE Artist='No One You Know' AND SongTitle IN ('Call Me Today', 'My Dog Spot')
AND Price > 1.00;
MongoDBã®
åã®äŸã§ç€ºããããã«ãMongoDB ã§ã¯ãšãªãäœæããäž»ãªæ¹æ³ã¯æ¬¡ã®ãšããã§ãã music
以äžã®äŸã§ã¯)ãè€æ°ã®ã³ã¬ã¯ã·ã§ã³ã®ã¯ãšãªã¯çŠæ¢ãããŠããŸãã
db.music.find( {
artist: "No One You Know"
}
);
db.music.find( {
artist: "No One You Know",
songTitle: /Call/
}
);
ããŒãã«ã®ãã¹ãŠã®è¡ãèªã¿åã
ãã¹ãŠã®è¡ã®èªã¿åãã¯ãåã«èª¬æããã¯ãšãª ãã¿ãŒã³ã®ç¹æ®ãªã±ãŒã¹ã«ãããŸããã
PostgreSQL
SELECT *
FROM Music;
ã«ãµã³ãã©
äžèšã® PostgreSQL ã®äŸãšäŒŒãŠããŸãã
MongoDBã®
db.music.find( {} );
ããŒãã«å ã®ããŒã¿ãç·šéãã
PostgreSQL
PostgreSQL ãæé ãæäŸããŸã UPDATE
ããŒã¿ãå€æŽããŸãã 圌女ã«ã¯ãã£ã³ã¹ããªã UPSERT
ãããã£ãŠãè¡ãããŒã¿ããŒã¹ã«ååšããªãå Žåããã®ã¹ããŒãã¡ã³ãã¯å€±æããŸãã
UPDATE Music
SET Genre = 'Disco'
WHERE Artist = 'The Acme Band' AND SongTitle = 'Still In Love';
ã«ãµã³ãã©
ã«ãµã³ãã©ã¯ UPDATE
PostgreSQLã«äŒŒãŠããŸãã UPDATE
åãã»ãã³ãã£ã¯ã¹ãæã£ãŠããŸã UPSERT
ã ã®ããã« INSERT
.
äžèšã® PostgreSQL ã®äŸãšäŒŒãŠããŸãã
MongoDBã®
æäœ UPSERT
ã è€æ°ã®ããã¥ã¡ã³ãã®æŽæ°ããã³åæ§ã®åäœ UPSERT
æäœã«è¿œå ã®ãã©ã°ãèšå®ããããšã§é©çšã§ããŸãã ããšãã°ã以äžã®äŸã§ã¯ãç¹å®ã®ã¢ãŒãã£ã¹ãã®ãžã£ã³ã«ããã®æ²ã«åºã¥ããŠæŽæ°ãããŸãã
db.music.update(
{"artist": "The Acme Band"},
{
$set: {
"genre": "Disco"
}
},
{"multi": true, "upsert": true}
);
ããŒãã«ããã®ããŒã¿ã®åé€
PostgreSQL
DELETE FROM Music
WHERE Artist = 'The Acme Band' AND SongTitle = 'Look Out, World';
ã«ãµã³ãã©
äžèšã® PostgreSQL ã®äŸãšäŒŒãŠããŸãã
MongoDBã®
MongoDBã«ã¯ãããã¥ã¡ã³ããåé€ããããã®XNUMXçš®é¡ã®æäœããããŸã-
db.music.deleteMany( {
artist: "The Acme Band"
}
);
ããŒãã«ãåé€ãã
PostgreSQL
DROP TABLE Music;
ã«ãµã³ãã©
äžèšã® PostgreSQL ã®äŸãšäŒŒãŠããŸãã
MongoDBã®
db.music.drop();
ãŸãšã
SQL ãš NoSQL ã®ã©ã¡ããéžæãããã«ã€ããŠã®è°è«ã¯ã10 幎以äžã«ããã£ãŠæ¿ãããªã£ãŠããŸãã ãã®è°è«ã«ã¯äž»ã« XNUMX ã€ã®åŽé¢ããããŸããããã¯ãããŒã¿ããŒã¹ ãšã³ãžã³ ã¢ãŒããã¯ã㣠(ã¢ããªã·ãã¯ãªãã©ã³ã¶ã¯ã·ã§ã³ SQL ãšåæ£åã®éãã©ã³ã¶ã¯ã·ã§ã³ NoSQL) ãšããŒã¿ããŒã¹èšèšã¢ãããŒã (SQL ã§ã®ããŒã¿ã®ã¢ãã«åãš NoSQL ã§ã®ã¯ãšãªã®ã¢ãã«å) ã§ãã
YugaByte DB ã®ãããªåæ£ãã©ã³ã¶ã¯ã·ã§ã³ ããŒã¿ããŒã¹ã䜿çšãããšãããŒã¿ããŒã¹ ã¢ãŒããã¯ãã£ã«é¢ããè°è«ãç°¡åã«çµããããããšãã§ããŸãã ããŒã¿éãåäžããŒãã«æžã蟌ããéãè¶ ãããšãèªåã·ã£ãŒãã£ã³ã°/ãªãã©ã³ã¹ã«ããç·åœ¢æžã蟌ã¿ã¹ã±ãŒã©ããªãã£ããµããŒãããå®å šåæ£ã¢ãŒããã¯ãã£ãå¿ èŠã«ãªããŸãã
ããã«ãããèšäºã§è¿°ã¹ãããã«ã
ããŒã¿ããŒã¹èšèšã®è°è«ã«æ»ããšãçŸå®äžçã®è€éãªã¢ããªã±ãŒã·ã§ã³ã«ã¯äž¡æ¹ã®èšèšã¢ãããŒã (SQL ãš NoSQL) ãå¿ èŠã§ãããšèšã£ãŠãéèšã§ã¯ãããŸããã SQL ã®ãããŒã¿ ã¢ããªã³ã°ãã¢ãããŒãã䜿çšãããšãéçºè ã¯å€åããããžãã¹èŠä»¶ã«ç°¡åã«å¯Ÿå¿ã§ããããã«ãªããŸããäžæ¹ãNoSQL ã®ãã¯ãšãª ã¢ããªã³ã°ãã¢ãããŒãã䜿çšãããšãåãéçºè ãäœé 延ãã€é«ã¹ã«ãŒãããã§å€§éã®ããŒã¿ãæäœã§ããããã«ãªããŸãã YugaByte DB ãã©ã¡ããã®ã¢ãããŒããæšé²ããã®ã§ã¯ãªããå ±éã®ã³ã¢ã§ SQL ããã³ NoSQL API ãæäŸããã®ã¯ãã®ããã§ãã ããã«ãYugaByte DB ã¯ãPostgreSQL ã Cassandra ãªã©ã®äžè¬çãªããŒã¿ããŒã¹èšèªãšã®äºææ§ãæäŸããããšã§ãéçºè ãåæ£åã®äžè²«æ§ã®é«ãããŒã¿ããŒã¹ ãšã³ãžã³ã䜿çšããããã«å¥ã®èšèªãåŠç¿ããå¿ èŠããªãããšãä¿èšŒããŸãã
ãã®èšäºã§ã¯ãPostgreSQLãCassandraãMongoDB ã®éã§ããŒã¿ããŒã¹èšèšã®åºæ¬ãã©ã®ããã«ç°ãªãããæ€èšããŸããã ä»åŸã®èšäºã§ã¯ãã€ã³ããã¯ã¹ããã©ã³ã¶ã¯ã·ã§ã³ãJOINãTTL ãã£ã¬ã¯ãã£ããJSON ããã¥ã¡ã³ããªã©ã®é«åºŠãªèšèšæŠå¿µã«ã€ããŠè©³ãã説æããŸãã
çŽ æŽãããé±æ«ããéãããã ããããã²ãåå ãã ããã
åºæïŒ habr.com