SELECT word, L2DistanceTransposed(vec, [-0.88693672, 1.31532824, -0.51182908, -0.99652702, 0.59907770], 16) AS distanceFROM fruit_animalORDER BY distance;
CREATE TABLE dbpedia( id String, title String, text String, vector Array(Float32) CODEC(NONE)) ENGINE = MergeTree ORDER BY (id);
コマンドラインからデータを挿入します:
for i in $(seq 0 25); do echo "ファイル ${i} を処理中..." clickhouse client -q "INSERT INTO dbpedia SELECT _id, title, text, \"text-embedding-3-large-1536-embedding\" FROM url('https://huggingface.co/api/datasets/Qdrant/dbpedia-entities-openai3-text-embedding-3-large-1536-1M/parquet/default/train/${i}.parquet') SETTINGS max_http_get_redirects=5,enable_url_encoding=0;" echo "ファイル ${i} 完了。"done
SELECT title, text, COUNT(DISTINCT concept) AS num_concepts_matched, MIN(distance) AS min_distance, AVG(distance) AS avg_distanceFROM ( ( SELECT title, text, 'Moon' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Moon'), 5) AS distance FROM dbpedia WHERE title != 'Moon' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Apollo 11' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Apollo 11'), 5) AS distance FROM dbpedia WHERE title != 'Apollo 11' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Space Shuttle' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Space Shuttle'), 5) AS distance FROM dbpedia WHERE title != 'Space Shuttle' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Astronaut' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Astronaut'), 5) AS distance FROM dbpedia WHERE title != 'Astronaut' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Rocket' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Rocket'), 5) AS distance FROM dbpedia WHERE title != 'Rocket' ORDER BY distance ASC LIMIT 1000 ) )WHERE title NOT IN ('Moon', 'Apollo 11', 'Space Shuttle', 'Astronaut', 'Rocket')GROUP BY title, textHAVING num_concepts_matched >= 3ORDER BY num_concepts_matched DESC, min_distance ASC LIMIT 10;
行 1:──────title: Aintree railway stationtext: For a guide to the various Aintree stations that have existed and their relationship to each other see Aintree Stations.Aintree railway station is a railway station in Aintree, Merseyside, England. It is on the Ormskirk branch of the Merseyrail network's Northern Line. Until 1968 it was known as Aintree Sefton Arms after a nearby public house. The station's design reflects the fact it is the closest station to Aintree Racecourse, where the annual Grand National horse race takes place.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 2:──────title: AP German Languagetext: Advanced Placement German Language (also known as AP German Language or AP German) is a course and examination provided by the College Board through the Advanced Placement Program. This course is designed to give high school students the opportunity to receive credit in a college-level German language course.Originally the College Board had offered two AP German exams, one with AP German Language and another with AP German Literature.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 3:──────title: Adelospondylitext: Adelospondyli is an order of elongate, presumably aquatic, Carboniferous amphibians. The skull is solidly roofed, and elongate, with the orbits located very far forward. The limbs are well developed. Most adelospondyls belong to the family Adelogyrinidae, although the adelospondyl Acherontiscus has been placed in its own family, Acherontiscidae. The group is restricted to the Mississippian (Serpukhovian Age) of Scotland.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 4:──────title: Adrien-Henri de Jussieutext: Adrien-Henri de Jussieu (23 December 1797 – 29 June 1853) was a French botanist.Born in Paris as the son of botanist Antoine Laurent de Jussieu, he received the degree of Doctor of Medicine in 1824 with a treatise of the plant family Euphorbiaceae. When his father retired in 1826, he succeeded him at the Jardin des Plantes; in 1845 he became professor of organography of plants.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 5:──────title: Alan Taylor (footballer, born 1953)text: Alan Taylor (born 14 November 1953) is an English former professional footballer best known for his goalscoring exploits with West Ham United in their FA Cup success of 1975, culminating in two goals in that season's final.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 6:──────title: Abstract algebraic logictext: In mathematical logic, abstract algebraic logic is the study of the algebraization of deductive systemsarising as an abstraction of the well-known Lindenbaum-Tarski algebra, and how the resulting algebras are related to logical systems.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 7:──────title: Ahsan Saleem Hyattext: General Ahsan Saleem Hayat (Urdu: احسن سلیم حیات; born 10 January 1948), is a retired four-star general who served as the vice chief of army staff of the Pakistan Army from 2004 until his retirement in 2007. Prior to that, he served as the operational field commander of the V Corps in Sindh Province and was a full-tenured professor of war studies at the National Defence University. He was succeeded by General Ashfaq Parvez Kayani on 8 October 2007.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 8:──────title: Al Wafa al Igatha al Islamiatext: There is another organization named Al Wafa (Israel), a charity, in Israel, devoted to womenThere is another organization Jamaiat Al-Wafa LiRayat Al-Musenin which is proscribed by the Israeli government.Al Wafa is an Islamic charity listed in Executive Order 13224 as an entity that supports terrorism.United States intelligence officials state that it was founded in Afghanistan by Adil Zamil Abdull Mohssin Al Zamil,Abdul Aziz al-Matrafi and Samar Khand.According to Saad Madai Saad al-Azmi's Combatant Status Review Tribunal Al Wafa is located in the Wazir Akhbar Khan area ofAfghanistan.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 9:───────title: Alex Baumanntext: Alexander Baumann, OC OOnt (born April 21, 1964) is a Canadian former competitive swimmer who won two gold medals and set two world records at the 1984 Summer Olympics in Los Angeles.Born in Prague (former Czechoslovakia), Baumann was raised in Canada after his family moved there in 1969 following the Prague Spring.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.9972260772085877行 10:───────title: Alberni-Clayoquot Regional Districttext: The Alberni-Clayoquot Regional District (2006 population 30,664) of British Columbia is located on west central Vancouver Island. Adjacent regional districts it shares borders with are the Strathcona and Comox Valley Regional Districts to the north, and the Nanaimo and Cowichan Valley Regional Districts to the east. The regional district offices are located in Port Alberni.num_concepts_matched: 5min_distance: 0.9971279086553189avg_distance: 0.997226077208587710 rows in set. Elapsed: 0.542 sec. Processed 5.01 million rows, 1.86 GB (9.24 million rows/s., 3.43 GB/s.)Peak memory usage: 327.04 MiB.
SELECT title, text, COUNT(DISTINCT concept) AS num_concepts_matched, MIN(distance) AS min_distance, AVG(distance) AS avg_distanceFROM ( ( SELECT title, text, 'Moon' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Moon'), 5) AS distance FROM dbpedia WHERE title != 'Moon' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Apollo 11' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Apollo 11'), 5) AS distance FROM dbpedia WHERE title != 'Apollo 11' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Space Shuttle' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Space Shuttle'), 5) AS distance FROM dbpedia WHERE title != 'Space Shuttle' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Astronaut' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Astronaut'), 5) AS distance FROM dbpedia WHERE title != 'Astronaut' ORDER BY distance ASC LIMIT 1000 ) UNION ALL ( SELECT title, text, 'Rocket' AS concept, L2DistanceTransposed(qbit, (SELECT vector FROM dbpedia WHERE title = 'Rocket'), 5) AS distance FROM dbpedia WHERE title != 'Rocket' ORDER BY distance ASC LIMIT 1000 ))WHERE title NOT IN ('Moon', 'Apollo 11', 'Space Shuttle', 'Astronaut', 'Rocket')GROUP BY title, textHAVING num_concepts_matched >= 3ORDER BY num_concepts_matched DESC, min_distance ASCLIMIT 10;
Row 1:──────title: Apollo programtext: The Apollo program, also known as Project Apollo, was the third United States human spaceflight program carried out by the National Aeronautics and Space Administration (NASA), which accomplished landing the first humans on the Moon from 1969 to 1972. First conceived during Dwight D. Eisenhower's administration as a three-man spacecraft to follow the one-man Project Mercury which put the first Americans in space, Apollo was later dedicated to President John F.num_concepts_matched: 4min_distance: 0.82420665avg_distance: 1.0207901149988174Row 2:──────title: Apollo 8text: Apollo 8, the second human spaceflight mission in the United States Apollo space program, was launched on December 21, 1968, and became the first manned spacecraft to leave Earth orbit, reach the Earth's Moon, orbit it and return safely to Earth.num_concepts_matched: 4min_distance: 0.8285278avg_distance: 1.0357224345207214Row 3:──────title: Lunar Orbiter 1text: The Lunar Orbiter 1 robotic (unmanned) spacecraft, part of the Lunar Orbiter Program, was the first American spacecraft to orbit the Moon. It was designed primarily to photograph smooth areas of the lunar surface for selection and verification of safe landing sites for the Surveyor and Apollo missions. It was also equipped to collect selenodetic, radiation intensity, and micrometeoroid impact data.The spacecraft was placed in an Earth parking orbit on August 10, 1966 at 19:31 (UTC).num_concepts_matched: 4min_distance: 0.94581836avg_distance: 1.0584313124418259Row 4:──────title: Apollo (spacecraft)text: The Apollo spacecraft was composed of three parts designed to accomplish the American Apollo program's goal of landing astronauts on the Moon by the end of the 1960s and returning them safely to Earth. The expendable (single-use) spacecraft consisted of a combined Command/Service Module (CSM) and a Lunar Module (LM).num_concepts_matched: 4min_distance: 0.9643517avg_distance: 1.0367188602685928Row 5:──────title: Surveyor 1text: Surveyor 1 was the first lunar soft-lander in the unmanned Surveyor program of the National Aeronautics and Space Administration (NASA, United States). This lunar soft-lander gathered data about the lunar surface that would be needed for the manned Apollo Moon landings that began in 1969.num_concepts_matched: 4min_distance: 0.9738264avg_distance: 1.0988530814647675Row 6:──────title: Spaceflighttext: Spaceflight (also written space flight) is ballistic flight into or through outer space. Spaceflight can occur with spacecraft with or without humans on board. Examples of human spaceflight include the Russian Soyuz program, the U.S. Space shuttle program, as well as the ongoing International Space Station. Examples of unmanned spaceflight include space probes that leave Earth orbit, as well as satellites in orbit around Earth, such as communications satellites.num_concepts_matched: 4min_distance: 0.9831049avg_distance: 1.060678943991661Row 7:──────title: Skylabtext: Skylab was a space station launched and operated by NASA and was the United States' first space station. Skylab orbited the Earth from 1973 to 1979, and included a workshop, a solar observatory, and other systems. It was launched unmanned by a modified Saturn V rocket, with a weight of 169,950 pounds (77 t). Three manned missions to the station, conducted between 1973 and 1974 using the Apollo Command/Service Module (CSM) atop the smaller Saturn IB, each delivered a three-astronaut crew.num_concepts_matched: 4min_distance: 0.99155205avg_distance: 1.0769911855459213Row 8:──────title: Orbital spaceflighttext: An orbital spaceflight (or orbital flight) is a spaceflight in which a spacecraft is placed on a trajectory where it could remain in space for at least one orbit. To do this around the Earth, it must be on a free trajectory which has an altitude at perigee (altitude at closest approach) above 100 kilometers (62 mi) (this is, by at least one convention, the boundary of space). To remain in orbit at this altitude requires an orbital speed of ~7.8 km/s.num_concepts_matched: 4min_distance: 1.0075209avg_distance: 1.085978478193283Row 9:───────title: Dragon (spacecraft)text: Dragon is a partially reusable spacecraft developed by SpaceX, an American private space transportation company based in Hawthorne, California. Dragon is launched into space by the SpaceX Falcon 9 two-stage-to-orbit launch vehicle, and SpaceX is developing a crewed version called the Dragon V2.During its maiden flight in December 2010, Dragon became the first commercially built and operated spacecraft to be recovered successfully from orbit.num_concepts_matched: 4min_distance: 1.0222818avg_distance: 1.0942841172218323Row 10:───────title: Space capsuletext: A space capsule is an often manned spacecraft which has a simple shape for the main section, without any wings or other features to create lift during atmospheric reentry.Capsules have been used in most of the manned space programs to date, including the world's first manned spacecraft Vostok and Mercury, as well as in later Soviet Voskhod, Soyuz, Zond/L1, L3, TKS, US Gemini, Apollo Command Module, Chinese Shenzhou and US, Russian and Indian manned spacecraft currently being developed.num_concepts_matched: 4min_distance: 1.0262821avg_distance: 1.0882147550582886
パフォーマンス: 10 rows in set. Elapsed: 1.157 sec. 処理済み: 1,000万行、32.76 GB (864万行/秒、28.32 GB/秒) Peak memory usage: 6.05 GiB。