在 《探索ClickHouse——安裝和測(cè)試》中，我們使用clickhouse直接從文件中讀取數(shù)據(jù)。clickhouse支持多種格式文件的導(dǎo)入導(dǎo)出，本節(jié)我們對(duì)此進(jìn)行分類介紹。
按常見(jiàn)格式區(qū)分
JSON
原始的JSON格式只支持導(dǎo)入，不支持導(dǎo)入。同時(shí)支持導(dǎo)入和導(dǎo)出的是其他幾種類型：
JSONEachRow
{"uuid_string":"{D22473F5-3802-7B40-E053-6C04A8C0A630}","price_string":"230000","time":"2021-06-09 00:00","postcode":"CO13 0PQ","a":"D","b":"N","c":"F","addr1":"4","addr2":"","street":"BELLAMY CLOSE","locality":"KIRBY CROSS","town":"FRINTON-ON-SEA","district":"TENDRING","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3803-7B40-E053-6C04A8C0A630}","price_string":"700000","time":"2021-06-25 00:00","postcode":"IG10 4BS","a":"T","b":"N","c":"F","addr1":"70","addr2":"","street":"SMARTS LANE","locality":"","town":"LOUGHTON","district":"EPPING FOREST","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3804-7B40-E053-6C04A8C0A630}","price_string":"625000","time":"2021-06-28 00:00","postcode":"EN9 1LH","a":"S","b":"N","c":"F","addr1":"7","addr2":"","street":"THE COBBINS","locality":"","town":"WALTHAM ABBEY","district":"EPPING FOREST","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3805-7B40-E053-6C04A8C0A630}","price_string":"360000","time":"2021-02-17 00:00","postcode":"SS9 3AU","a":"S","b":"N","c":"F","addr1":"117","addr2":"","street":"FLEMMING AVENUE","locality":"","town":"LEIGH-ON-SEA","district":"SOUTHEND-ON-SEA","county":"SOUTHEND-ON-SEA","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3806-7B40-E053-6C04A8C0A630}","price_string":"345000","time":"2021-06-25 00:00","postcode":"SS15 6BG","a":"S","b":"N","c":"F","addr1":"2","addr2":"","street":"BROADWATER GREEN","locality":"LAINDON","town":"BASILDON","district":"BASILDON","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3807-7B40-E053-6C04A8C0A630}","price_string":"220000","time":"2021-08-20 00:00","postcode":"CO7 0HE","a":"S","b":"N","c":"F","addr1":"133","addr2":"","street":"CHAPEL ROAD","locality":"BRIGHTLINGSEA","town":"COLCHESTER","district":"TENDRING","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3808-7B40-E053-6C04A8C0A630}","price_string":"362000","time":"2021-06-29 00:00","postcode":"SS15 4AX","a":"T","b":"N","c":"F","addr1":"32","addr2":"","street":"CROUCH STREET","locality":"","town":"BASILDON","district":"BASILDON","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3809-7B40-E053-6C04A8C0A630}","price_string":"300000","time":"2021-06-25 00:00","postcode":"CM9 6EP","a":"T","b":"N","c":"F","addr1":"58","addr2":"","street":"ORCHARD ROAD","locality":"","town":"MALDON","district":"MALDON","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-380A-7B40-E053-6C04A8C0A630}","price_string":"300000","time":"2021-06-17 00:00","postcode":"SS4 3AR","a":"T","b":"N","c":"F","addr1":"32","addr2":"","street":"ALLERTON CLOSE","locality":"","town":"ROCHFORD","district":"ROCHFORD","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-380B-7B40-E053-6C04A8C0A630}","price_string":"535000","time":"2021-10-22 00:00","postcode":"SS9 1PZ","a":"T","b":"N","c":"F","addr1":"113","addr2":"","street":"LEIGHTON AVENUE","locality":"","town":"LEIGH-ON-SEA","district":"SOUTHEND-ON-SEA","county":"SOUTHEND-ON-SEA","d":"A","e":"A"}
JSONStringsEachRow
{"uuid_string":"{D22473F5-3802-7B40-E053-6C04A8C0A630}","price_string":"230000","time":"2021-06-09 00:00","postcode":"CO13 0PQ","a":"D","b":"N","c":"F","addr1":"4","addr2":"","street":"BELLAMY CLOSE","locality":"KIRBY CROSS","town":"FRINTON-ON-SEA","district":"TENDRING","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3803-7B40-E053-6C04A8C0A630}","price_string":"700000","time":"2021-06-25 00:00","postcode":"IG10 4BS","a":"T","b":"N","c":"F","addr1":"70","addr2":"","street":"SMARTS LANE","locality":"","town":"LOUGHTON","district":"EPPING FOREST","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3804-7B40-E053-6C04A8C0A630}","price_string":"625000","time":"2021-06-28 00:00","postcode":"EN9 1LH","a":"S","b":"N","c":"F","addr1":"7","addr2":"","street":"THE COBBINS","locality":"","town":"WALTHAM ABBEY","district":"EPPING FOREST","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3805-7B40-E053-6C04A8C0A630}","price_string":"360000","time":"2021-02-17 00:00","postcode":"SS9 3AU","a":"S","b":"N","c":"F","addr1":"117","addr2":"","street":"FLEMMING AVENUE","locality":"","town":"LEIGH-ON-SEA","district":"SOUTHEND-ON-SEA","county":"SOUTHEND-ON-SEA","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3806-7B40-E053-6C04A8C0A630}","price_string":"345000","time":"2021-06-25 00:00","postcode":"SS15 6BG","a":"S","b":"N","c":"F","addr1":"2","addr2":"","street":"BROADWATER GREEN","locality":"LAINDON","town":"BASILDON","district":"BASILDON","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3807-7B40-E053-6C04A8C0A630}","price_string":"220000","time":"2021-08-20 00:00","postcode":"CO7 0HE","a":"S","b":"N","c":"F","addr1":"133","addr2":"","street":"CHAPEL ROAD","locality":"BRIGHTLINGSEA","town":"COLCHESTER","district":"TENDRING","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3808-7B40-E053-6C04A8C0A630}","price_string":"362000","time":"2021-06-29 00:00","postcode":"SS15 4AX","a":"T","b":"N","c":"F","addr1":"32","addr2":"","street":"CROUCH STREET","locality":"","town":"BASILDON","district":"BASILDON","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-3809-7B40-E053-6C04A8C0A630}","price_string":"300000","time":"2021-06-25 00:00","postcode":"CM9 6EP","a":"T","b":"N","c":"F","addr1":"58","addr2":"","street":"ORCHARD ROAD","locality":"","town":"MALDON","district":"MALDON","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-380A-7B40-E053-6C04A8C0A630}","price_string":"300000","time":"2021-06-17 00:00","postcode":"SS4 3AR","a":"T","b":"N","c":"F","addr1":"32","addr2":"","street":"ALLERTON CLOSE","locality":"","town":"ROCHFORD","district":"ROCHFORD","county":"ESSEX","d":"A","e":"A"}
{"uuid_string":"{D22473F5-380B-7B40-E053-6C04A8C0A630}","price_string":"535000","time":"2021-10-22 00:00","postcode":"SS9 1PZ","a":"T","b":"N","c":"F","addr1":"113","addr2":"","street":"LEIGHTON AVENUE","locality":"","town":"LEIGH-ON-SEA","district":"SOUTHEND-ON-SEA","county":"SOUTHEND-ON-SEA","d":"A","e":"A"}
JSONCompactEachRow
["{D22473F5-3802-7B40-E053-6C04A8C0A630}", "230000", "2021-06-09 00:00", "CO13 0PQ", "D", "N", "F", "4", "", "BELLAMY CLOSE", "KIRBY CROSS", "FRINTON-ON-SEA", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3803-7B40-E053-6C04A8C0A630}", "700000", "2021-06-25 00:00", "IG10 4BS", "T", "N", "F", "70", "", "SMARTS LANE", "", "LOUGHTON", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3804-7B40-E053-6C04A8C0A630}", "625000", "2021-06-28 00:00", "EN9 1LH", "S", "N", "F", "7", "", "THE COBBINS", "", "WALTHAM ABBEY", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3805-7B40-E053-6C04A8C0A630}", "360000", "2021-02-17 00:00", "SS9 3AU", "S", "N", "F", "117", "", "FLEMMING AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
["{D22473F5-3806-7B40-E053-6C04A8C0A630}", "345000", "2021-06-25 00:00", "SS15 6BG", "S", "N", "F", "2", "", "BROADWATER GREEN", "LAINDON", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3807-7B40-E053-6C04A8C0A630}", "220000", "2021-08-20 00:00", "CO7 0HE", "S", "N", "F", "133", "", "CHAPEL ROAD", "BRIGHTLINGSEA", "COLCHESTER", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3808-7B40-E053-6C04A8C0A630}", "362000", "2021-06-29 00:00", "SS15 4AX", "T", "N", "F", "32", "", "CROUCH STREET", "", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3809-7B40-E053-6C04A8C0A630}", "300000", "2021-06-25 00:00", "CM9 6EP", "T", "N", "F", "58", "", "ORCHARD ROAD", "", "MALDON", "MALDON", "ESSEX", "A", "A"]
["{D22473F5-380A-7B40-E053-6C04A8C0A630}", "300000", "2021-06-17 00:00", "SS4 3AR", "T", "N", "F", "32", "", "ALLERTON CLOSE", "", "ROCHFORD", "ROCHFORD", "ESSEX", "A", "A"]
["{D22473F5-380B-7B40-E053-6C04A8C0A630}", "535000", "2021-10-22 00:00", "SS9 1PZ", "T", "N", "F", "113", "", "LEIGHTON AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
JSONCompactStringsEachRow
["{D22473F5-3802-7B40-E053-6C04A8C0A630}", "230000", "2021-06-09 00:00", "CO13 0PQ", "D", "N", "F", "4", "", "BELLAMY CLOSE", "KIRBY CROSS", "FRINTON-ON-SEA", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3803-7B40-E053-6C04A8C0A630}", "700000", "2021-06-25 00:00", "IG10 4BS", "T", "N", "F", "70", "", "SMARTS LANE", "", "LOUGHTON", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3804-7B40-E053-6C04A8C0A630}", "625000", "2021-06-28 00:00", "EN9 1LH", "S", "N", "F", "7", "", "THE COBBINS", "", "WALTHAM ABBEY", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3805-7B40-E053-6C04A8C0A630}", "360000", "2021-02-17 00:00", "SS9 3AU", "S", "N", "F", "117", "", "FLEMMING AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
["{D22473F5-3806-7B40-E053-6C04A8C0A630}", "345000", "2021-06-25 00:00", "SS15 6BG", "S", "N", "F", "2", "", "BROADWATER GREEN", "LAINDON", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3807-7B40-E053-6C04A8C0A630}", "220000", "2021-08-20 00:00", "CO7 0HE", "S", "N", "F", "133", "", "CHAPEL ROAD", "BRIGHTLINGSEA", "COLCHESTER", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3808-7B40-E053-6C04A8C0A630}", "362000", "2021-06-29 00:00", "SS15 4AX", "T", "N", "F", "32", "", "CROUCH STREET", "", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3809-7B40-E053-6C04A8C0A630}", "300000", "2021-06-25 00:00", "CM9 6EP", "T", "N", "F", "58", "", "ORCHARD ROAD", "", "MALDON", "MALDON", "ESSEX", "A", "A"]
["{D22473F5-380A-7B40-E053-6C04A8C0A630}", "300000", "2021-06-17 00:00", "SS4 3AR", "T", "N", "F", "32", "", "ALLERTON CLOSE", "", "ROCHFORD", "ROCHFORD", "ESSEX", "A", "A"]
["{D22473F5-380B-7B40-E053-6C04A8C0A630}", "535000", "2021-10-22 00:00", "SS9 1PZ", "T", "N", "F", "113", "", "LEIGHTON AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
按分隔符
TabSeparated
{D22473F5-3802-7B40-E053-6C04A8C0A630} 230000 2021-06-09 00:00 CO13 0PQ D N F 4 BELLAMY CLOSE KIRBY CROSS FRINTON-ON-SEA TENDRING ESSEX A A
{D22473F5-3803-7B40-E053-6C04A8C0A630} 700000 2021-06-25 00:00 IG10 4BS T N F 70 SMARTS LANE LOUGHTON EPPING FOREST ESSEX A A
{D22473F5-3804-7B40-E053-6C04A8C0A630} 625000 2021-06-28 00:00 EN9 1LH S N F 7 THE COBBINS WALTHAM ABBEY EPPING FOREST ESSEX A A
{D22473F5-3805-7B40-E053-6C04A8C0A630} 360000 2021-02-17 00:00 SS9 3AU S N F 117 FLEMMING AVENUE LEIGH-ON-SEA SOUTHEND-ON-SEA SOUTHEND-ON-SEA A A
{D22473F5-3806-7B40-E053-6C04A8C0A630} 345000 2021-06-25 00:00 SS15 6BG S N F 2 BROADWATER GREEN LAINDON BASILDON BASILDON ESSEX A A
{D22473F5-3807-7B40-E053-6C04A8C0A630} 220000 2021-08-20 00:00 CO7 0HE S N F 133 CHAPEL ROAD BRIGHTLINGSEA COLCHESTER TENDRING ESSEX A A
{D22473F5-3808-7B40-E053-6C04A8C0A630} 362000 2021-06-29 00:00 SS15 4AX T N F 32 CROUCH STREET BASILDON BASILDON ESSEX A A
{D22473F5-3809-7B40-E053-6C04A8C0A630} 300000 2021-06-25 00:00 CM9 6EP T N F 58 ORCHARD ROAD MALDON MALDON ESSEX A A
{D22473F5-380A-7B40-E053-6C04A8C0A630} 300000 2021-06-17 00:00 SS4 3AR T N F 32 ALLERTON CLOSE ROCHFORD ROCHFORD ESSEX A A
{D22473F5-380B-7B40-E053-6C04A8C0A630} 535000 2021-10-22 00:00 SS9 1PZ T N F 113 LEIGHTON AVENUE LEIGH-ON-SEA SOUTHEND-ON-SEA SOUTHEND-ON-SEA A A
CSV
"{D22473F5-3802-7B40-E053-6C04A8C0A630}","230000","2021-06-09 00:00","CO13 0PQ","D","N","F","4","","BELLAMY CLOSE","KIRBY CROSS","FRINTON-ON-SEA","TENDRING","ESSEX","A","A"
"{D22473F5-3803-7B40-E053-6C04A8C0A630}","700000","2021-06-25 00:00","IG10 4BS","T","N","F","70","","SMARTS LANE","","LOUGHTON","EPPING FOREST","ESSEX","A","A"
"{D22473F5-3804-7B40-E053-6C04A8C0A630}","625000","2021-06-28 00:00","EN9 1LH","S","N","F","7","","THE COBBINS","","WALTHAM ABBEY","EPPING FOREST","ESSEX","A","A"
"{D22473F5-3805-7B40-E053-6C04A8C0A630}","360000","2021-02-17 00:00","SS9 3AU","S","N","F","117","","FLEMMING AVENUE","","LEIGH-ON-SEA","SOUTHEND-ON-SEA","SOUTHEND-ON-SEA","A","A"
"{D22473F5-3806-7B40-E053-6C04A8C0A630}","345000","2021-06-25 00:00","SS15 6BG","S","N","F","2","","BROADWATER GREEN","LAINDON","BASILDON","BASILDON","ESSEX","A","A"
"{D22473F5-3807-7B40-E053-6C04A8C0A630}","220000","2021-08-20 00:00","CO7 0HE","S","N","F","133","","CHAPEL ROAD","BRIGHTLINGSEA","COLCHESTER","TENDRING","ESSEX","A","A"
"{D22473F5-3808-7B40-E053-6C04A8C0A630}","362000","2021-06-29 00:00","SS15 4AX","T","N","F","32","","CROUCH STREET","","BASILDON","BASILDON","ESSEX","A","A"
"{D22473F5-3809-7B40-E053-6C04A8C0A630}","300000","2021-06-25 00:00","CM9 6EP","T","N","F","58","","ORCHARD ROAD","","MALDON","MALDON","ESSEX","A","A"
"{D22473F5-380A-7B40-E053-6C04A8C0A630}","300000","2021-06-17 00:00","SS4 3AR","T","N","F","32","","ALLERTON CLOSE","","ROCHFORD","ROCHFORD","ESSEX","A","A"
"{D22473F5-380B-7B40-E053-6C04A8C0A630}","535000","2021-10-22 00:00","SS9 1PZ","T","N","F","113","","LEIGHTON AVENUE","","LEIGH-ON-SEA","SOUTHEND-ON-SEA","SOUTHEND-ON-SEA","A","A"
Values
(‘{D22473F5-3802-7B40-E053-6C04A8C0A630}’,‘230000’,‘2021-06-09 00:00’,‘CO13 0PQ’,‘D’,‘N’,‘F’,‘4’,‘’,‘BELLAMY CLOSE’,‘KIRBY CROSS’,‘FRINTON-ON-SEA’,‘TENDRING’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-3803-7B40-E053-6C04A8C0A630}’,‘700000’,‘2021-06-25 00:00’,‘IG10 4BS’,‘T’,‘N’,‘F’,‘70’,‘’,‘SMARTS LANE’,‘’,‘LOUGHTON’,‘EPPING FOREST’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-3804-7B40-E053-6C04A8C0A630}’,‘625000’,‘2021-06-28 00:00’,‘EN9 1LH’,‘S’,‘N’,‘F’,‘7’,‘’,‘THE COBBINS’,‘’,‘WALTHAM ABBEY’,‘EPPING FOREST’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-3805-7B40-E053-6C04A8C0A630}’,‘360000’,‘2021-02-17 00:00’,‘SS9 3AU’,‘S’,‘N’,‘F’,‘117’,‘’,‘FLEMMING AVENUE’,‘’,‘LEIGH-ON-SEA’,‘SOUTHEND-ON-SEA’,‘SOUTHEND-ON-SEA’,‘A’,‘A’),(‘{D22473F5-3806-7B40-E053-6C04A8C0A630}’,‘345000’,‘2021-06-25 00:00’,‘SS15 6BG’,‘S’,‘N’,‘F’,‘2’,‘’,‘BROADWATER GREEN’,‘LAINDON’,‘BASILDON’,‘BASILDON’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-3807-7B40-E053-6C04A8C0A630}’,‘220000’,‘2021-08-20 00:00’,‘CO7 0HE’,‘S’,‘N’,‘F’,‘133’,‘’,‘CHAPEL ROAD’,‘BRIGHTLINGSEA’,‘COLCHESTER’,‘TENDRING’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-3808-7B40-E053-6C04A8C0A630}’,‘362000’,‘2021-06-29 00:00’,‘SS15 4AX’,‘T’,‘N’,‘F’,‘32’,‘’,‘CROUCH STREET’,‘’,‘BASILDON’,‘BASILDON’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-3809-7B40-E053-6C04A8C0A630}’,‘300000’,‘2021-06-25 00:00’,‘CM9 6EP’,‘T’,‘N’,‘F’,‘58’,‘’,‘ORCHARD ROAD’,‘’,‘MALDON’,‘MALDON’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-380A-7B40-E053-6C04A8C0A630}’,‘300000’,‘2021-06-17 00:00’,‘SS4 3AR’,‘T’,‘N’,‘F’,‘32’,‘’,‘ALLERTON CLOSE’,‘’,‘ROCHFORD’,‘ROCHFORD’,‘ESSEX’,‘A’,‘A’),(‘{D22473F5-380B-7B40-E053-6C04A8C0A630}’,‘535000’,‘2021-10-22 00:00’,‘SS9 1PZ’,‘T’,‘N’,‘F’,‘113’,‘’,‘LEIGHTON AVENUE’,‘’,‘LEIGH-ON-SEA’,‘SOUTHEND-ON-SEA’,‘SOUTHEND-ON-SEA’,‘A’,‘A’)
按攜帶更多信息
只攜帶字段名
TabSeparatedWithNames
uuid_string price_string time postcode a b c addr1 addr2 street locality town district county d e
{D22473F5-3802-7B40-E053-6C04A8C0A630} 230000 2021-06-09 00:00 CO13 0PQ D N F 4 BELLAMY CLOSE KIRBY CROSS FRINTON-ON-SEA TENDRING ESSEX A A
{D22473F5-3803-7B40-E053-6C04A8C0A630} 700000 2021-06-25 00:00 IG10 4BS T N F 70 SMARTS LANE LOUGHTON EPPING FOREST ESSEX A A
{D22473F5-3804-7B40-E053-6C04A8C0A630} 625000 2021-06-28 00:00 EN9 1LH S N F 7 THE COBBINS WALTHAM ABBEY EPPING FOREST ESSEX A A
{D22473F5-3805-7B40-E053-6C04A8C0A630} 360000 2021-02-17 00:00 SS9 3AU S N F 117 FLEMMING AVENUE LEIGH-ON-SEA SOUTHEND-ON-SEA SOUTHEND-ON-SEA A A
{D22473F5-3806-7B40-E053-6C04A8C0A630} 345000 2021-06-25 00:00 SS15 6BG S N F 2 BROADWATER GREEN LAINDON BASILDON BASILDON ESSEX A A
{D22473F5-3807-7B40-E053-6C04A8C0A630} 220000 2021-08-20 00:00 CO7 0HE S N F 133 CHAPEL ROAD BRIGHTLINGSEA COLCHESTER TENDRING ESSEX A A
{D22473F5-3808-7B40-E053-6C04A8C0A630} 362000 2021-06-29 00:00 SS15 4AX T N F 32 CROUCH STREET BASILDON BASILDON ESSEX A A
{D22473F5-3809-7B40-E053-6C04A8C0A630} 300000 2021-06-25 00:00 CM9 6EP T N F 58 ORCHARD ROAD MALDON MALDON ESSEX A A
{D22473F5-380A-7B40-E053-6C04A8C0A630} 300000 2021-06-17 00:00 SS4 3AR T N F 32 ALLERTON CLOSE ROCHFORD ROCHFORD ESSEX A A
{D22473F5-380B-7B40-E053-6C04A8C0A630} 535000 2021-10-22 00:00 SS9 1PZ T N F 113 LEIGHTON AVENUE LEIGH-ON-SEA SOUTHEND-ON-SEA SOUTHEND-ON-SEA A A
CSVWithNames
"uuid_string","price_string","time","postcode","a","b","c","addr1","addr2","street","locality","town","district","county","d","e"
"{D22473F5-3802-7B40-E053-6C04A8C0A630}","230000","2021-06-09 00:00","CO13 0PQ","D","N","F","4","","BELLAMY CLOSE","KIRBY CROSS","FRINTON-ON-SEA","TENDRING","ESSEX","A","A"
"{D22473F5-3803-7B40-E053-6C04A8C0A630}","700000","2021-06-25 00:00","IG10 4BS","T","N","F","70","","SMARTS LANE","","LOUGHTON","EPPING FOREST","ESSEX","A","A"
"{D22473F5-3804-7B40-E053-6C04A8C0A630}","625000","2021-06-28 00:00","EN9 1LH","S","N","F","7","","THE COBBINS","","WALTHAM ABBEY","EPPING FOREST","ESSEX","A","A"
"{D22473F5-3805-7B40-E053-6C04A8C0A630}","360000","2021-02-17 00:00","SS9 3AU","S","N","F","117","","FLEMMING AVENUE","","LEIGH-ON-SEA","SOUTHEND-ON-SEA","SOUTHEND-ON-SEA","A","A"
"{D22473F5-3806-7B40-E053-6C04A8C0A630}","345000","2021-06-25 00:00","SS15 6BG","S","N","F","2","","BROADWATER GREEN","LAINDON","BASILDON","BASILDON","ESSEX","A","A"
"{D22473F5-3807-7B40-E053-6C04A8C0A630}","220000","2021-08-20 00:00","CO7 0HE","S","N","F","133","","CHAPEL ROAD","BRIGHTLINGSEA","COLCHESTER","TENDRING","ESSEX","A","A"
"{D22473F5-3808-7B40-E053-6C04A8C0A630}","362000","2021-06-29 00:00","SS15 4AX","T","N","F","32","","CROUCH STREET","","BASILDON","BASILDON","ESSEX","A","A"
"{D22473F5-3809-7B40-E053-6C04A8C0A630}","300000","2021-06-25 00:00","CM9 6EP","T","N","F","58","","ORCHARD ROAD","","MALDON","MALDON","ESSEX","A","A"
"{D22473F5-380A-7B40-E053-6C04A8C0A630}","300000","2021-06-17 00:00","SS4 3AR","T","N","F","32","","ALLERTON CLOSE","","ROCHFORD","ROCHFORD","ESSEX","A","A"
"{D22473F5-380B-7B40-E053-6C04A8C0A630}","535000","2021-10-22 00:00","SS9 1PZ","T","N","F","113","","LEIGHTON AVENUE","","LEIGH-ON-SEA","SOUTHEND-ON-SEA","SOUTHEND-ON-SEA","A","A"
攜帶字段名和類型
TabSeparatedWithNamesAndTypes
uuid_string price_string time postcode a b c addr1 addr2 street locality town district county d e
String String String String String String String String String String String String String String String String
{D22473F5-3802-7B40-E053-6C04A8C0A630} 230000 2021-06-09 00:00 CO13 0PQ D N F 4 BELLAMY CLOSE KIRBY CROSS FRINTON-ON-SEA TENDRING ESSEX A A
{D22473F5-3803-7B40-E053-6C04A8C0A630} 700000 2021-06-25 00:00 IG10 4BS T N F 70 SMARTS LANE LOUGHTON EPPING FOREST ESSEX A A
{D22473F5-3804-7B40-E053-6C04A8C0A630} 625000 2021-06-28 00:00 EN9 1LH S N F 7 THE COBBINS WALTHAM ABBEY EPPING FOREST ESSEX A A
{D22473F5-3805-7B40-E053-6C04A8C0A630} 360000 2021-02-17 00:00 SS9 3AU S N F 117 FLEMMING AVENUE LEIGH-ON-SEA SOUTHEND-ON-SEA SOUTHEND-ON-SEA A A
{D22473F5-3806-7B40-E053-6C04A8C0A630} 345000 2021-06-25 00:00 SS15 6BG S N F 2 BROADWATER GREEN LAINDON BASILDON BASILDON ESSEX A A
{D22473F5-3807-7B40-E053-6C04A8C0A630} 220000 2021-08-20 00:00 CO7 0HE S N F 133 CHAPEL ROAD BRIGHTLINGSEA COLCHESTER TENDRING ESSEX A A
{D22473F5-3808-7B40-E053-6C04A8C0A630} 362000 2021-06-29 00:00 SS15 4AX T N F 32 CROUCH STREET BASILDON BASILDON ESSEX A A
{D22473F5-3809-7B40-E053-6C04A8C0A630} 300000 2021-06-25 00:00 CM9 6EP T N F 58 ORCHARD ROAD MALDON MALDON ESSEX A A
{D22473F5-380A-7B40-E053-6C04A8C0A630} 300000 2021-06-17 00:00 SS4 3AR T N F 32 ALLERTON CLOSE ROCHFORD ROCHFORD ESSEX A A
{D22473F5-380B-7B40-E053-6C04A8C0A630} 535000 2021-10-22 00:00 SS9 1PZ T N F 113 LEIGHTON AVENUE LEIGH-ON-SEA SOUTHEND-ON-SEA SOUTHEND-ON-SEA A A
JSONCompactEachRowWithNamesAndTypes
["uuid_string", "price_string", "time", "postcode", "a", "b", "c", "addr1", "addr2", "street", "locality", "town", "district", "county", "d", "e"]
["String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String"]
["{D22473F5-3802-7B40-E053-6C04A8C0A630}", "230000", "2021-06-09 00:00", "CO13 0PQ", "D", "N", "F", "4", "", "BELLAMY CLOSE", "KIRBY CROSS", "FRINTON-ON-SEA", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3803-7B40-E053-6C04A8C0A630}", "700000", "2021-06-25 00:00", "IG10 4BS", "T", "N", "F", "70", "", "SMARTS LANE", "", "LOUGHTON", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3804-7B40-E053-6C04A8C0A630}", "625000", "2021-06-28 00:00", "EN9 1LH", "S", "N", "F", "7", "", "THE COBBINS", "", "WALTHAM ABBEY", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3805-7B40-E053-6C04A8C0A630}", "360000", "2021-02-17 00:00", "SS9 3AU", "S", "N", "F", "117", "", "FLEMMING AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
["{D22473F5-3806-7B40-E053-6C04A8C0A630}", "345000", "2021-06-25 00:00", "SS15 6BG", "S", "N", "F", "2", "", "BROADWATER GREEN", "LAINDON", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3807-7B40-E053-6C04A8C0A630}", "220000", "2021-08-20 00:00", "CO7 0HE", "S", "N", "F", "133", "", "CHAPEL ROAD", "BRIGHTLINGSEA", "COLCHESTER", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3808-7B40-E053-6C04A8C0A630}", "362000", "2021-06-29 00:00", "SS15 4AX", "T", "N", "F", "32", "", "CROUCH STREET", "", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3809-7B40-E053-6C04A8C0A630}", "300000", "2021-06-25 00:00", "CM9 6EP", "T", "N", "F", "58", "", "ORCHARD ROAD", "", "MALDON", "MALDON", "ESSEX", "A", "A"]
["{D22473F5-380A-7B40-E053-6C04A8C0A630}", "300000", "2021-06-17 00:00", "SS4 3AR", "T", "N", "F", "32", "", "ALLERTON CLOSE", "", "ROCHFORD", "ROCHFORD", "ESSEX", "A", "A"]
["{D22473F5-380B-7B40-E053-6C04A8C0A630}", "535000", "2021-10-22 00:00", "SS9 1PZ", "T", "N", "F", "113", "", "LEIGHTON AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
JSONCompactStringsEachRowWithNamesAndTypes
["uuid_string", "price_string", "time", "postcode", "a", "b", "c", "addr1", "addr2", "street", "locality", "town", "district", "county", "d", "e"]
["String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String", "String"]
["{D22473F5-3802-7B40-E053-6C04A8C0A630}", "230000", "2021-06-09 00:00", "CO13 0PQ", "D", "N", "F", "4", "", "BELLAMY CLOSE", "KIRBY CROSS", "FRINTON-ON-SEA", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3803-7B40-E053-6C04A8C0A630}", "700000", "2021-06-25 00:00", "IG10 4BS", "T", "N", "F", "70", "", "SMARTS LANE", "", "LOUGHTON", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3804-7B40-E053-6C04A8C0A630}", "625000", "2021-06-28 00:00", "EN9 1LH", "S", "N", "F", "7", "", "THE COBBINS", "", "WALTHAM ABBEY", "EPPING FOREST", "ESSEX", "A", "A"]
["{D22473F5-3805-7B40-E053-6C04A8C0A630}", "360000", "2021-02-17 00:00", "SS9 3AU", "S", "N", "F", "117", "", "FLEMMING AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
["{D22473F5-3806-7B40-E053-6C04A8C0A630}", "345000", "2021-06-25 00:00", "SS15 6BG", "S", "N", "F", "2", "", "BROADWATER GREEN", "LAINDON", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3807-7B40-E053-6C04A8C0A630}", "220000", "2021-08-20 00:00", "CO7 0HE", "S", "N", "F", "133", "", "CHAPEL ROAD", "BRIGHTLINGSEA", "COLCHESTER", "TENDRING", "ESSEX", "A", "A"]
["{D22473F5-3808-7B40-E053-6C04A8C0A630}", "362000", "2021-06-29 00:00", "SS15 4AX", "T", "N", "F", "32", "", "CROUCH STREET", "", "BASILDON", "BASILDON", "ESSEX", "A", "A"]
["{D22473F5-3809-7B40-E053-6C04A8C0A630}", "300000", "2021-06-25 00:00", "CM9 6EP", "T", "N", "F", "58", "", "ORCHARD ROAD", "", "MALDON", "MALDON", "ESSEX", "A", "A"]
["{D22473F5-380A-7B40-E053-6C04A8C0A630}", "300000", "2021-06-17 00:00", "SS4 3AR", "T", "N", "F", "32", "", "ALLERTON CLOSE", "", "ROCHFORD", "ROCHFORD", "ESSEX", "A", "A"]
["{D22473F5-380B-7B40-E053-6C04A8C0A630}", "535000", "2021-10-22 00:00", "SS9 1PZ", "T", "N", "F", "113", "", "LEIGHTON AVENUE", "", "LEIGH-ON-SEA", "SOUTHEND-ON-SEA", "SOUTHEND-ON-SEA", "A", "A"]
TSKV
uuid_string={D22473F5-3802-7B40-E053-6C04A8C0A630} price_string=230000 time=2021-06-09 00:00 postcode=CO13 0PQ a=D b=N c=F addr1=4 addr2= street=BELLAMY CLOSE locality=KIRBY CROSS town=FRINTON-ON-SEA district=TENDRING county=ESSEX d=A e=A
uuid_string={D22473F5-3803-7B40-E053-6C04A8C0A630} price_string=700000 time=2021-06-25 00:00 postcode=IG10 4BS a=T b=N c=F addr1=70 addr2= street=SMARTS LANE locality= town=LOUGHTON district=EPPING FOREST county=ESSEX d=A e=A
uuid_string={D22473F5-3804-7B40-E053-6C04A8C0A630} price_string=625000 time=2021-06-28 00:00 postcode=EN9 1LH a=S b=N c=F addr1=7 addr2= street=THE COBBINS locality= town=WALTHAM ABBEY district=EPPING FOREST county=ESSEX d=A e=A
uuid_string={D22473F5-3805-7B40-E053-6C04A8C0A630} price_string=360000 time=2021-02-17 00:00 postcode=SS9 3AU a=S b=N c=F addr1=117 addr2= street=FLEMMING AVENUE locality= town=LEIGH-ON-SEA district=SOUTHEND-ON-SEA county=SOUTHEND-ON-SEA d=A e=A
uuid_string={D22473F5-3806-7B40-E053-6C04A8C0A630} price_string=345000 time=2021-06-25 00:00 postcode=SS15 6BG a=S b=N c=F addr1=2 addr2= street=BROADWATER GREEN locality=LAINDON town=BASILDON district=BASILDON county=ESSEX d=A e=A
uuid_string={D22473F5-3807-7B40-E053-6C04A8C0A630} price_string=220000 time=2021-08-20 00:00 postcode=CO7 0HE a=S b=N c=F addr1=133 addr2= street=CHAPEL ROAD locality=BRIGHTLINGSEA town=COLCHESTER district=TENDRING county=ESSEX d=A e=A
uuid_string={D22473F5-3808-7B40-E053-6C04A8C0A630} price_string=362000 time=2021-06-29 00:00 postcode=SS15 4AX a=T b=N c=F addr1=32 addr2= street=CROUCH STREET locality= town=BASILDON district=BASILDON county=ESSEX d=A e=A
uuid_string={D22473F5-3809-7B40-E053-6C04A8C0A630} price_string=300000 time=2021-06-25 00:00 postcode=CM9 6EP a=T b=N c=F addr1=58 addr2= street=ORCHARD ROAD locality= town=MALDON district=MALDON county=ESSEX d=A e=A
uuid_string={D22473F5-380A-7B40-E053-6C04A8C0A630} price_string=300000 time=2021-06-17 00:00 postcode=SS4 3AR a=T b=N c=F addr1=32 addr2= street=ALLERTON CLOSE locality= town=ROCHFORD district=ROCHFORD county=ESSEX d=A e=A
uuid_string={D22473F5-380B-7B40-E053-6C04A8C0A630} price_string=535000 time=2021-10-22 00:00 postcode=SS9 1PZ a=T b=N c=F addr1=113 addr2= street=LEIGHTON AVENUE locality= town=LEIGH-ON-SEA district=SOUTHEND-ON-SEA county=SOUTHEND-ON-SEA d=A e=A
RowBinaryWithNamesAndTypes
二進(jìn)制格式，不支持文本查看。
不可讀類型
Native
最高性能的格式。通過(guò)二進(jìn)制格式的塊進(jìn)行寫(xiě)入和讀取（即它是基于塊的，這和后面介紹的RowBinary系列不同）。它主要用于服務(wù)器間交互。
在《探索ClickHouse——使用Projection加速查詢》一文中，原始數(shù)據(jù)是CSV格式，大小約4.6G。導(dǎo)入數(shù)據(jù)庫(kù)后約308M。這個(gè)數(shù)據(jù)庫(kù)的內(nèi)容導(dǎo)出為Native格式的文件，大小只有約168M。
-rw-rw-r-- 1 fangliang fangliang 176572374 Sep 26 09:19 Native.txt
RowBinary
以二進(jìn)制格式逐行格式化和解析數(shù)據(jù)。行和值連續(xù)列出，沒(méi)有分隔符。 這種格式比 Native 格式效率低，因?yàn)樗腔谛械摹?br> 導(dǎo)出這個(gè)類型的文件，大概和Native類型的差不多大小。
-rw-rw-r-- 1 fangliang fangliang 176568308 Sep 26 10:12 RowBinary.txt
RowBinaryWithNamesAndTypes
添加頭部信息的RowBinary。
其他開(kāi)源項(xiàng)目類型
Protobuf
Protobuf是一個(gè) Protocol Buffers 格式。此格式需要外部格式描述文件(proto文件)。 該描述文件會(huì)進(jìn)行緩存，以備后續(xù)查詢。 ClickHouse支持 proto2 和 proto3 語(yǔ)法的proto文件，支持重復(fù)/可選/必填字段。
Avro
Apache Avro 是在Apache Hadoop項(xiàng)目中開(kāi)發(fā)的面向行的數(shù)據(jù)序列化框架。
Parquet
Apache Parquet 是Hadoop生態(tài)系統(tǒng)中普遍使用的列式存儲(chǔ)格式。
Arrow
Apache Arrow是一種用于內(nèi)存數(shù)據(jù)庫(kù)的格式，共有兩種模式，文件與流模式。
ArrowStream
ArrowStream對(duì)應(yīng)的是Arrow的流模式，這種格式適用于內(nèi)存的流式處理。
ORC
Apache ORC 是Hadoop生態(tài)系統(tǒng)中普遍存在的列式存儲(chǔ)格式。
其他
Compact和非Compact的區(qū)別
非Compact是一條完整符合規(guī)則的格式，Compact則是將值變成一個(gè)數(shù)組保存。以JSONCompactEachRowJSONEachRow為例
# JSONCompactEachRow
["{D22473F5-3802-7B40-E053-6C04A8C0A630}", "230000", "2021-06-09 00:00", "CO13 0PQ", "D", "N", "F", "4", "", "BELLAMY CLOSE", "KIRBY CROSS", "FRINTON-ON-SEA", "TENDRING", "ESSEX", "A", "A"]
# JSONEachRow
{"uuid_string":"{D22473F5-3802-7B40-E053-6C04A8C0A630}","price_string":"230000","time":"2021-06-09 00:00","postcode":"CO13 0PQ","a":"D","b":"N","c":"F","addr1":"4","addr2":"","street":"BELLAMY CLOSE","locality":"KIRBY CROSS","town":"FRINTON-ON-SEA","district":"TENDRING","county":"ESSEX","d":"A","e":"A"}
String和非String的區(qū)別
String會(huì)將JSON對(duì)象轉(zhuǎn)變?yōu)橐粋€(gè)字符串。以JSONStringsEachRow和JSONEachRow為例:文章來(lái)源：http://www.zghlxwxcb.cn/news/detail-729721.html
# JSONCompactEachRow
[42,"hello",[1,"a"]]
# JSONCompactStringsEachRow
["42","hello","(2,'a')"]
導(dǎo)出命令
上述格式可以通過(guò)修改下面的命令導(dǎo)出，其中–format用于指定格式。文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-729721.html
clickhouse-client --query "SELECT * FROM file('pp-2021.csv', 'CSV', 'uuid_string String, price_string String, time String, postcode String, a String, b String, c String, addr1 String, addr2 String, street String, locality String, town String, district String, county String, d String, e String') LIMIT 10;" --format JSONCompactEachRow > JSONCompactEachRow.txt
查考資料
- https://clickhouse.com/docs/en/sql-reference/formats
到了這里，關(guān)于探索ClickHouse——同時(shí)支持導(dǎo)入導(dǎo)出功能的文件格式的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
