投稿時間:2022-03-23 09:41:25 RSSフィード2022-03-23 09:00 分まとめ(45件)

カテゴリー等	サイト名等	記事タイトル・トレンドワード等	リンクURL	頻出ワード・要約等/検索ボリューム	登録日
IT	ITmedia 総合記事一覧	[ITmedia ビジネスオンライン] 「ネットを全く利用していない」66歳以上の33％、理由は？	https://www.itmedia.co.jp/business/articles/2203/23/news054.html	itmedia	2022-03-23 08:54:00
IT	ITmedia 総合記事一覧	[ITmedia ビジネスオンライン] 「派遣の求人が改善した」6割、具体的には？	https://www.itmedia.co.jp/business/articles/2203/23/news050.html	itmedia	2022-03-23 08:53:00
IT	ITmedia 総合記事一覧	[ITmedia ビジネスオンライン] “外泊すると家賃が下がる”賃貸　東急が東京・押上に第2弾	https://www.itmedia.co.jp/business/articles/2203/23/news058.html	itmedia	2022-03-23 08:50:00
IT	ITmedia 総合記事一覧	[ITmedia News] AppleのApp StoreやApple Musicなどがまた約1時間「機能停止」（復旧済み）	https://www.itmedia.co.jp/news/articles/2203/23/news071.html	apple	2022-03-23 08:25:00
AWS	AWS Desktop and Application Streaming Blog	Generate logon messages for security and compliance in Amazon WorkSpaces	https://aws.amazon.com/blogs/desktop-and-application-streaming/generate-logon-messages-for-security-and-compliance-in-amazon-windows-workspaces/	Generate logon messages for security and compliance in Amazon WorkSpacesCustomers can require that users be prompted with a security or legal notice upon logon to restricted systems For example the notice might indicate that the system is for “official use only restricted to access by “authorized personnel or highlight that activity may be audited Microsoft Windows provides a mechanism to generate these message prompts …	2022-03-22 23:11:48
AWS	AWSタグが付けられた新着投稿 - Qiita	AWS公式資料で挑むSCS認定(21)-こんな時どうする	https://qiita.com/mingchun_zhao/items/1eb8ca9bd126fd36d705	AWS公式資料で挑むSCS認定こんな時どうする前回AWS公式資料で挑むSCS認定こんな時どうするはじめに前回に続き「こんな時どうする」をまとめ中です。	2022-03-23 08:54:06
Docker	dockerタグが付けられた新着投稿 - Qiita	Dockerイメージとコンテナの削除方法	https://qiita.com/EdgeDevice/items/2b3d26ef57d39be73505	dockerrmiimageid	2022-03-23 08:20:14
技術ブログ	Developers.IO	【DataSync】タスクの実行ステータスをEventBridge+SNSで通知してみた	https://dev.classmethod.jp/articles/datasync-task-exec-notice-via-eventbridge-sns/	ashissan	2022-03-22 23:30:51
海外TECH	DEV Community	ELK: Logstash in 5 minutes	https://dev.to/thehunter896/elk-logstash-in-5-minutes-530o	ELK Logstash in minutesThis is a continuation to the previous article Elasticsearch in minutes Logstash is the second essential tool for Data Engineering Like Elasticsearch it is developed and maintained by the Elastic team and it also has a free version available which will cover all your needs In this article we will learn what Logstash is and how it works we will also set up a Logstash processor and play with some of the plugins it has to offer All in under minutes For this article I have set up a Github Repository where you can find all the files needed to complete some of the steps as well as the images within this article What is LogstashLogstash is a data processor it acts as the pipeline between your databases or nodes and other resources which could be other databases nodes or applications However the true power of Logstash comes in its data manipulation functions Transforming and Filtering operations Where Logstash ShinesData ProcessingData Streaming EnviornmentsData Analytics EnvironmentsWhy does it shine in these enviromentsHigh compatibility with input and output resources Logstash can read from almost anything including Cloud applications message Brokers and websockets Here is the list for input and output plugins Fast and flexible transformation functions Transform data structures with simple JSON defined operations Filtering Huge range of filtering functions are your disposal you can use this in different pipelines to create multiple sources from one raw source What version are we setting upWe will be setting up a free Logstash processor version xStep DockerFor ease of use we will be using a Centos Docker container Pull the image docker pull centos You can find the image hereRun the container with SYS Admin permissions and a mounted volume docker run id cap add SYS ADMIN name logstash centos v sys fs cgroup sys fs cgroup ro centos sbin initWe run the container with SYS ADMIN permissions because we need systemctl to work inside the containerConnect to the container docker exec it logstash centos bin bashStep Machine SetupUpdate the machine yum update y amp amp yum upgrade yInstall a text editor any yum install vim y Step Install LogstashInstall the GPG key for elastic sudo rpm import Add this logstash repo under etc yum repo d logstash repo logstash x name Elastic repository for x packagesbaseurl gpgcheck gpgkey enabled autorefresh type rpm mdRemember to save Download and install the repoyum install logstashStart logstashsystemctl start logstashVerify logstash is runningsystemctl status logstashNow stop logstash we ll restart it later down the linesystemctl stop logstashStep set up an elastic nodeWe will be using Elasticsearch as input for our pipelines so you will need to set up another Docker image with an elastic node you can find a guide here if you don t have one already running Annotate the IPs of your containersBy default Docker sets up a network called bridge which containers can use to communicate between themselves you can see the IPs by first seeing your container IDs then inspecting the container docker psdocker inspect docker container id Docker inspect will yield a very large JSON search for the field IPAddressAnnotate the IPs for both containers we ll use them in the next step Create a test pipeline index and insert sample data you need to do this inside your Elasticsearch container You can copy this sample data and paste it under tmp sample data jsonvim tmp sample data jsonCheck the index has been created correctlycurl X GET cacert etc elasticsearch certs http ca crt u elastic https localhost cat indices Index this sample data we previously savedcurl X POST cacert etc elasticsearch certs http ca crt u elastic H Content Type application nx ndjson https localhost cat indices data binary tmp sample data jsonCreate another index called test pipeline outputDue to time constraints hint on the title we are going to take some shortcuts Disabling SSL and Setting our replicas to To do disable ssl you need only change xpac security enabled to false under the file etc elasticsearch elasticsearch yml Restart elasticsearch systemctl restart elasticsearchNow set your replicas to do this step for both test pipeline and test pipeline output indices curl X PUT u elastic http localhost test pipeline settings H Content Type application json data index number of replicas Step set up a logstash pipelineCopy this configuration under etc logstash conf d pipeline confThe purpose of this pipeline is to get the messages from index test pipeline to another index called test pipeline outputThe only thing left now is to restart logstash in our logstash docker container systemctl restart logstash Let it run for a couple of minutes after that check indices in your Elasticsearch node curl X GET cacert etc elasticsearch certs http ca crt u elastic http localhost cat indices In the image you can see that the output index has items while the original has this is because logstash will continuously run this pipeline every few seconds In any case with this article we have successfully set up a Logstash processor and moved data around with a very basic setup Follow me for part where we ll set up a Kibana UI in minutes	2022-03-22 23:19:18
Apple	AppleInsider - Frontpage News	Apple hit with second iCloud services outage in two days	https://appleinsider.com/articles/22/03/22/apple-hit-with-second-icloud-services-outage-in-two-days?utm_medium=rss	Apple hit with second iCloud services outage in two daysApple is hit with another round of outages with Apple Music Podcasts and both iOS and macOS App Stores affected Starting at pm ET on Tuesday many of Apple s services went offline as an apparent outage affected the Cupertino tech giant for the second time in two days As always many users took to social media to report that they d noticed the interruption in service Read more	2022-03-22 23:03:23
金融	金融総合：経済レポート一覧	FSBレポ統計の日本分集計結果（2022年2月）	http://www3.keizaireport.com/report.php/RID/488778/?rss	日本銀行	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	デジタル人民元の足元の動向：国際金融トピックスNo.9	http://www3.keizaireport.com/report.php/RID/488781/?rss	国際通貨研究所	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	FX Daily（3月21日）～ドル円、119円台半ばまで上昇	http://www3.keizaireport.com/report.php/RID/488782/?rss	fxdaily	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	オーストラリアの保険資本規制LAGIC等の改正を巡る動向：保険・年金フォーカス	http://www3.keizaireport.com/report.php/RID/488785/?rss	lagic	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	SWIFT制裁で始まる中露・印露の国際決済協力～ロシアと中国が国際銀行決済ネットワークの連携を検討...：木内登英のGlobal Economy ＆ Policy Insight	http://www3.keizaireport.com/report.php/RID/488786/?rss	lobaleconomypolicyinsight	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	日米金融政策デカップリングで円は120円台に突入～日本銀行は政策変更を否定し円安を容認する姿勢を示す...：木内登英のGlobal Economy ＆ Policy Insight	http://www3.keizaireport.com/report.php/RID/488792/?rss	lobaleconomypolicyinsight	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	アジア主要通貨・株価の動き（3月18日まで）	http://www3.keizaireport.com/report.php/RID/488810/?rss	国際金融情報センター	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	グリーンリカバリーのために投資家ができることとは	http://www3.keizaireport.com/report.php/RID/488812/?rss	eyjapan	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	守谷輸送機工業（東証二部）～国内における大型荷物用エレベーターの製造・販売・据付・保守・修理事業を展開。新工場の建設による国内需要の取り込みで成長を目指す：アナリストレポート	http://www3.keizaireport.com/report.php/RID/488820/?rss	取り込み	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	第4回 企業のサイバーセキュリティ対策に関する実態調査～過去のサイバーセキュリティ事故発生有無：16.3%「ある」と回答。サイバー保険の加入企業割合は26.0％	http://www3.keizaireport.com/report.php/RID/488822/?rss	実態調査	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	マンスリー通貨レポート（2022年3月）～為替レートと為替市場における当面のテーマ	http://www3.keizaireport.com/report.php/RID/488823/?rss	為替レート	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	ウィークリーレポート 2022年3月22日号～米国株式は6週ぶりに上昇。	http://www3.keizaireport.com/report.php/RID/488824/?rss	三井住友トラスト	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	米国の株式市場と債券市場、どちらの見通しが正しい？：基礎研レポート	http://www3.keizaireport.com/report.php/RID/488828/?rss	債券市場	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	みずほ経済・金融ウィークリー 2022年2月22日号～先週の内外経済・金融市場動向・評価＆今週の注目点	http://www3.keizaireport.com/report.php/RID/488831/?rss	金融市場	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	自社株買いレポート（2022年3月号）～2022年1-2月期の自社株買いの公表金額（取得枠設定）は、前年同期比6,614億円増（118.8％増）の1兆2,179億円	http://www3.keizaireport.com/report.php/RID/488833/?rss	前年同期	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	ポートフォリオの分散効果について：フィナンシャルエンジニアリングレポート Vol.38	http://www3.keizaireport.com/report.php/RID/488834/?rss	発表	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	強まる円安圧力とドル円相場 / ユーロ:ECBのタカ派化で利上げ期待は高まるが... / メキシコペソ:弱い実体経済とインフレの狭間で中銀は？：Weekly FX Market Focus	http://www3.keizaireport.com/report.php/RID/488835/?rss	weeklyfxmarketfocus	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	投資環境ウィークリー 2022年3月22日号【日本、米国、欧州、中国】～ウクライナ紛争に伴う一次産品価格の上昇は世界景気を下押しするのか	http://www3.keizaireport.com/report.php/RID/488836/?rss	一次産品	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	週間市場レポート（2022年3月14日～3月18日）～日本の株式・債券市場、米国の株式市場、外国為替市場	http://www3.keizaireport.com/report.php/RID/488837/?rss	債券市場	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	日銀金融政策決定会合 大規模金融緩和の維持を決定～新型コロナウイルスの感染動向と資源価格の高騰がリスク要因	http://www3.keizaireport.com/report.php/RID/488838/?rss	新型コロナウイルス	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	【注目検索キーワード】メタバース	http://search.keizaireport.com/search.php/-/keyword=メタバース/?rss	検索キーワード	2022-03-23 00:00:00
金融	金融総合：経済レポート一覧	【お薦め書籍】5秒でチェック、すぐに使える! 2行でわかるサクサク仕事ノート	https://www.amazon.co.jp/exec/obidos/ASIN/4046053631/keizaireport-22/	結集	2022-03-23 00:00:00
金融	日本銀行：RSS	BIS国際資金取引統計および国際与信統計の日本分集計結果	http://www.boj.or.jp/statistics/bis/ibs/index.htm	集計	2022-03-23 08:50:00
ニュース	BBC News - Home	P&O Ferries defends cuts as some workers to get £100,000	https://www.bbc.co.uk/news/business-60840467?at_medium=RSS&at_campaign=KARANGA	compensation	2022-03-22 23:32:49
ニュース	BBC News - Home	US rolls back Trump-era tariffs on UK steel	https://www.bbc.co.uk/news/business-60839343?at_medium=RSS&at_campaign=KARANGA	european	2022-03-22 23:11:30
ニュース	BBC News - Home	Paul Pogba: Burglars stole World Cup winner's medal, Man Utd & France midfielder says	https://www.bbc.co.uk/sport/football/60843380?at_medium=RSS&at_campaign=KARANGA	Paul Pogba Burglars stole World Cup winner x s medal Man Utd amp France midfielder saysManchester United midfielder Paul Pogba says burglars stole his World Cup winner s medal when they broke into his home	2022-03-22 23:45:32
ニュース	BBC News - Home	The Papers: Sunak told to 'spike hike' while Mariupol turns to 'ashes'	https://www.bbc.co.uk/news/blogs-the-papers-60842997?at_medium=RSS&at_campaign=KARANGA	insurance	2022-03-22 23:03:52
ビジネス	ダイヤモンド・オンライン - 新着記事	バイデン氏、大半のロシア議員対象とする制裁準備＝米当局者 - WSJ発	https://diamond.jp/articles/-/299854	議員	2022-03-23 08:07:00
北海道	北海道新聞	薬物使用でリオ五輪金メダル剥奪　重量挙げのラヒモフ	https://www.hokkaido-np.co.jp/article/660002/	重量挙げ	2022-03-23 08:33:00
北海道	北海道新聞	フィギュア鍵山が中京大へ　合格通知で「試合に集中」	https://www.hokkaido-np.co.jp/article/660001/	集中	2022-03-23 08:33:00
北海道	北海道新聞	台湾東部でＭ６・６　全土に揺れ、震度６弱も	https://www.hokkaido-np.co.jp/article/659976/	中央気象局	2022-03-23 08:26:04
北海道	北海道新聞	エネルギー供給確保へ協調　米長官、ＩＥＡで議論	https://www.hokkaido-np.co.jp/article/659998/	長官	2022-03-23 08:09:00
北海道	北海道新聞	全体練習で投内連係など確認　日本ハム	https://www.hokkaido-np.co.jp/article/659960/	全体練習	2022-03-23 08:06:50
ビジネス	プレジデントオンライン	サウナがあるのに水風呂がない…日本唯一の｢トヨタグループのホテル｣がお客の要望より大切にすること - 自動車とホテル､共通するのは｢顧客へのおもてなしの心｣	https://president.jp/articles/-/55730	長野県茅野市	2022-03-23 09:00:00
GCP	Cloud Blog	Using GeoJSON in BigQuery for geospatial analytics	https://cloud.google.com/blog/topics/developers-practitioners/using-geojson-bigquery-geospatial-analytics/	Using GeoJSON in BigQuery for geospatial analyticsThe first step in most analytical workloads is to ingest the data that you need for your analysis into your data warehouse For geospatial analysis involving point line or polygon data ingesting data can be complex because geospatial data comes in myriad data formats Two of the most popular geospatial formats are GeoJSON and GeoJSON NL newline delimited geoJSON This blog describes how to use the new GeoJSON NL load functionality in BigQuery to load data from geoJSON NL files directly into BigQuery tables We ll also cover the geoJSON NL format and its benefits show you some simple tools to transform various spatial data formats into GeoJSON NL and walk through a hands on code example Click to enlargeBigQuery s geospatial support allows users to import and analyze GEOGRAPHY data types made up of points lines and polygons at massive scales right in their data warehouse instead of specialized geospatial tools To make it easier for you to use geospatial data with BigQuery we recently added direct support for GeoJSON NL files using bq load and external tables What is a GeoJSON NL file GeoJSON NL stands for newline delimited GeoJSON  GeoJSON NL files consist of geoJSON geographic features with each feature separated by a newline “ n character GeoJSON NL files differ from GeoJSON files in that instead of a single “FeatureCollection object with multiple “Feature objects in a GeoJSON file a newline delimited GeoJSON file will have only “Feature object each on its own unique line Geojson format filecode block StructValue u code u r n type FeatureCollection r n features r n r n type Feature r n properties name Melbourne r n geometry type Point coordinates r n r n type Feature r n properties name Canberra r n geometry type Point coordinates r n r n type Feature r n properties name Sydney r n geometry type Point coordinates r n r n r n u language u The preceding code block is a a geoJSON format file The following shows an equivalent geoJSON NL formatted filecode block StructValue u code u type Feature properties name Melbourne geometry type Point coordinates r n type Feature properties name Canberra geometry type Point coordinates r n type Feature properties name Sydney geometry type Point coordinates u language u An easy way to tell the difference between the two formats is to check if there is a “FeatureCollection JSON object in the file then it is GeoJSON If there is exactly one “Feature JSON object on each line then it is GeoJSON NL Why geoJSON NL as a spatial format GeoJSON is a great spatial file format because it works in a broad number of applications across many platforms and is a subset of JSON However the problem with GeoJSON is that all features are stored as a “FeatureCollection object in a large array Parsing a large array with large JSON strings is a performance bottleneck All features stored in the array must be loaded and parsed into memory by a single CPU thread from the source file leading to ETL jobs that fail due to out of memory errors If there are issues with any one feature in a FeatureCollection the entire parse amp load job may fail Newline delimited geoJSON solves the problems of standard geoJSON files by storing each feature on a unique line instead of in an array enabling higher performance and efficiency for applications working with a large amount of spatial data such as streaming IoT example Features can be read and written from multiple threads or a distributed process and each feature can be parsed on it s own lowering the memory footprint and allowing more robust schema and geometry validation Converting spatial data to geoJSON NLYou can convert your spatial data to geoJSON NL format using a variety of open source and proprietary tools The section below includes code examples for converting geoJSON shapefiles GPX KML and CSV files to geoJSON NL You can use the code examples below using the GCP Cloud Shell  easiest to start your local development machine  Linux or MacOS based or as a GCP Cloud Function using a Node js or Python runtime environment  jq  Install a command line tool for working with JSON data which can be used to convert a geoJSON file or object to a geoJSON NL file code block StructValue u code u cat in geojson jq c features gt out geojsonl u language u geojsonndjson a node js command line tool for converting geoJSON to GeoJSON NL code block StructValue u code u npm install g geojsonndjson r ngeojsonndjson in geojson gt out geojsonl u language u Other libraries can be combined with jq and geojsonndjson for converting spatial formats such as CSV KML shapefiles and GPX into geoJSON NL for use with BQ shapefile a node js command line tool for converting shapefiles to other spatial formats including geojson nl using the newline delimited option  Example code block StructValue u code u npm install g shapefile r nshpjson newline delimited in shp o out geojsonl u language u togeojson a node js command line tool for converting KML TCX and GPX files to geoJSON Example code block StructValue u code u npm install g tmcw togeojson cli r ntogeojson in kml jq c features gt out geojsonl u language u csvgeojson a node js command line tool for converting CSV files to geoJSON Specify the lat and lon parameters to identify the latitude and longitude column header names Example code block StructValue u code u npm install g csvgeojson r ncsvgeojson ulat uclat ud ulon uclng ud in csv jq c features gt out geojsonl u language u Fiona a Python spatial package for reading and writing most common spatial data formats for conversion analytics and scripting workloads GDAL a powerful command line spatial toolset which includes the ogrogr command for converting between most popular geospatial file formats and more complex geospatial functions such as coordinate system transformations FME a partner tool that allows ingestion of myriad types and formats  Super useful Requires a commercial license Working with GeoJSON NL in BigQueryYou can load GeoJSON NL data into a BQ table either using BigQuery s command line tool “bq load or by using external tables  bq load works with the GCP Cloud SDK installed on your development machine or via the GCP Cloud Console The general structure to import a geoJSON NL file into a bq table using bq load is code block StructValue u code u bq load source format NEWLINE DELIMITED JSON json extension GEOJSON autodetect dataset tablename mydata geojsonl u language u As an example we can use bq load with the autodetect flag to ingest a geoJSON NL file containing the single Feature using below resulting in the following BQ table and schema code block StructValue u code u type Feature id ABC properties zone mean geometry type Point coordinates u language u To import a geoJSON NL format file specify the source format and json extension code block StructValue u code u source format NEWLINE DELIMETED JSON r n json extension GEOJSON u language u You can either autodetect or specify the schema of the table imported from a geoJSON NL file All data in the “geometry keys of your features will be loaded into a table column named “geometry with data type “GEOGRAPHY Other table field names and data types will be parsed from the “properties object and “id objects  Alternatively you can create an external table from a GeoJSON NL file stored in Bigtable Cloud Storage or Google Drive External tables are useful for “ELT extract load transform workloads where you need to load and clean your data in one command as well as for joining to external data that frequently updates such as a real time weather forecast External tables have a number of limitations so only use them if appropriate for your use case To create an external table from a geoJSON NL file stored in a cloud storage bucket docs adapt the example SQL code below in the BQ console code block StructValue u code u CREATE EXTERNAL TABLE dataset tablename OPTIONS r n format NEWLINE DELIMITED JSON r n json extension GEOJSON r n uris gs mybucket mydata geojsonl r n u language u Hands on code example Airbnb listing analyticsLet s get started analyzing geoJSON NL data in BigQuery with an example  If you d like to follow along you can log into your BigQuery workspace here and if you don t have one yet you can get set up in less than minute and for free with the BigQuery Sandbox Let s say you re planning a vacation to Hawaii and you d like to know which neighborhoods on which islands have the largest selection of listings in your price range In order to do this analysis you need listing stats and neighborhood boundary data from Airbnb Fortunately Airbnb makes its data available in aggregate under the creative commons license on data hubs like opendatasoft which we ll use for this example Prerequisites Access to BigQuery Sandbox and Cloud ConsoleLoading the data into BigQuery  Create a new GCP project and check that billing is enabled or use the BigQuery Sandbox  Create a new GCS bucket with a unique name in your project with all the default settings For this code example we ll use the bucket name “geojson bq example however note that you ll need to create a unique bucket name and reference it for your example  Create a dataset in your BQ workspace named “geojson examples  Open the BigQuery UI and activate Cloud Shell Next we ll download the data for our analysis transform it to geojson nl format and store it in Cloud Storage We can do this all in Cloud Shell using cURL and gsutil   There are two source data files a “listings data file containing Airbnb listings in Hawaii in CSV format with specific metrics such as availability price and the listing location as longitude latitude pairs b “neighborhood data file in geoJSON format containing the names of each neighborhood in Hawaii along with their polygon geometries First we ll get the Airbnb listings data for Hawaii from opendatasoft with cURL and then upload it to our Cloud Storage Bucket with gsutil   a We do not need to transform the listings data CSV into geoJSON NL format because it already contains “point data as longitude and latitude data in a single column We will transform the column to a GEOGRAPHY data type in the following step after we load the file into BQ b Enter the following commands into Cloud Console code block StructValue u code u LISTING URL public download format csv amp disjunctive neighbourhood true amp disjunctive column true amp disjunctive city true amp refine column United states amp refine city Hawaii amp timezone America Los Angeles amp lang en amp use labels for header true amp csv separator B r n download file and pipe to stdout r ncurl s LISTING URL amp gt dev stdout r n send output csv format data to cloud storage bucket r ngsutil cp gs geojson bq example airbnb hi listings csv u language u  Then we ll get the Hawaii Airbnb neighborhood data in geojson format from opendatasoft using cURL transform it from geojson to geoJSON NL format using jq and upload it to our Cloud Storage Bucket with gsutil   a Enter the following commands into Cloud Shell code block StructValue u code u NEIGHBORHOOD URL public download format geojson amp disjunctive room type true amp refine location United states Hawaii amp timezone America Los Angeles amp lang en r n download file and pipe to stdout r ncurl s NEIGHBORHOOD URL amp gt dev stdout r n transform geojson to geojson nl format using jq r njq c features r n send output geojson nl format data to cloud storage bucket r ngsutil cp gs geojson bq example airbnb hi neighborhoods geojsonl u language u Now that our source data is staged in Cloud Storage we can load the data into BigQuery using bq load First load “airbnb hi neighborhoods geojsonl file from cloud storage into a BigQuery table named geojson examples airbnb hi neighborhoods Enter the following command into Cloud Shell code block StructValue u code u bq load source format NEWLINE DELIMITED JSON r n json extension GEOJSON autodetect r ngeojson examples airbnb hi neighborhoods r ngs geojson bq example airbnb hi neighborhoods geojsonl u language u Then load the “airbnb hi listings csv file from the GCS bucket into a BigQuery table named geojson examples airbnb hi listings Enter the following command into Cloud Shell code block StructValue u code u bq load source format CSV autodetect skip leading rows r n field delimiter allow quoted newlines r ngeojson examples airbnb hi locations r ngs geojson bq example airbnb hi listings csv u language u Analyzing the data in BigQueryNow that our Airbnb data for the state of Hawaii is loaded into BQ we can analyze it using BQ geospatial functions First let s have a look at the neighborhoods in the lists   Create a table with a native “geography point column with clustering on the geography column from the “airbnb hi locations table This increases the speed of geospatial analytics such as a “point in polygon query using ST Contains in the WHERE clause which we ll use in the next step to aggregate listing metrics by neighborhood geometry a Read more about transforming geospatial data and handling improperly formatted spatial data in the BQ docs code block StructValue u code u CREATE TABLE geojson examples locations geo r nCLUSTER BY geometry r nAS r n SELECT r n r n ST GEOGPOINT r n CAST split Coordinates OFFSET as float r n CAST split Coordinates OFFSET as float r n as geometry r n FROM geojson examples airbnb hi locations r n u language u Note that the geometry data is in “latitude longitude format in the source file so we reverse the order in the SQL query above when creating a GEOGRAPHY type point with ST GEOGPOINT Check the result of the query by creating a visualization in GeoViz code block StructValue u code u Select FROM geojson examples locations geo LIMIT u language u  Execute a query to count the number of Airbnb listings in each HI neighborhood using the geographic function “ST Contains   The “ST Contains function performs a spatial join between the geographic features In this case we re checking the “point of each Airbnb listing to determine if it s within the geographic “polygon of a neighborhood in Hawaii If the point is within the neighborhood polygon the function will return “true else it will return “false We group the query by “neighborhood and aggregate and return in descending order the number of listings in the group by clause with a “count function code block StructValue u code u SELECT r n count as num listings r n neighborhoods neighbourhood r n FROM geojson examples airbnb hi neighborhoods as neighborhoods r n INNER JOIN geojson examples locations geo locations ON ST CONTAINS neighborhoods geometry locations geometry r n GROUP BY neighborhoods neighbourhood r n ORDER BY num listings desc u language u Now we ll create a clean neighborhood geometry table with one row per neighborhood store it as a new table and visualize the table output in GeoViz Note the use of the ANY VALUE aggregate function to return a column with data type GEOGRAPHY in a query with a GROUP BY clause code block StructValue u code u CREATE TABLE geojson examples locations neighborhood r nCLUSTER BY geometry r nAS r n SELECT r n neighbourhood neighborhood r n country r n city r n ANY VALUE geometry geometry r nFROM geojson examples airbnb hi neighborhoods r nGROUP BY neighbourhood country city r n u language u Now we can create a query to understand which neighborhood has the most listings highest average room price best availability or most reviews for “entire home apt listings on Airbnb in Hawaii and visualize the results in GeoViz code block StructValue u code u SELECT r n neighborhoods neighborhood r n count as num listings r n avg Room Price as avg price r n sum Number of reviews as num reviews r n avg Availibility as avg availability r n ANY VALUE neighborhoods geometry neighborhood geometry r nFROM geojson examples locations neighborhood as neighborhoods r n INNER JOIN geojson examples locations geo locations ON r n ST CONTAINS neighborhoods geometry locations geometry r nWHERE locations Room type Entire home apt r nGROUP BY neighborhoods neighborhood u language u Creating a Data Studio dashboardNow we ll create an interactive dashboard in Data Studio to explore different questions from the data and determine the best neighborhood for our vacation After running the query from step above click Explore Data gt Data Studio from the results panel in BigQuery In the Data Studio GUI for the BigQuery custom SQL data source that just created create a “Table chart and drag drop metrics from the “available fields column to the “Metric column  Now we ll create a filled map chart from the neighborhood polygon geometry Click “Add a chart gt “Filled Map and configure the Fields Location Geospatial field and Color metric as per the screenshot below  Now add two filters to the Data Studio dashboard one for “avg price and the other for “num listings These filters will enable the user to ask questions of the data to find neighborhoods that meet their vacation selection criteria  With this Data Studio dashboard we can answer our original question which neighborhoods on which islands have the best selection of Airbnb s in our price range Filter the average listing price to suit our budget below per night and a large volume of Airbnb listing options in the neighborhood gt listings available The Primary Urban Center on O ahu Puna on Hawai i and Kapaa Wallis on Kaua i neighborhoods all meet our price and popularity criteria so we ll focus our Airbnb vacation rental search there ConclusionA new feature in BigQuery allows users to load geoJSON NL files directly into tables with GEOMETRY columns using bq load GeoJSON NL file support makes it faster and easier to load point linestring and polygon spatial data from into your BigQuery analytics workloads  Try working with geoJSON NL data in BigQuery today Related ArticleLoading geospatial data into BigQuery just got easier with FMELoad geospatial data into BigQuery using FME to load transform and ingest GIS file types for data analytics Read Article	2022-03-22 23:15:00