da Agency

Extensions for connectivity and new data types now available in Cloud SQL for PostgreSQL

Open source database PostgreSQL is designed to be easily extensible through its support of extensions. When an extension is loaded into a database, it can function just like features that are built-in. This adds additional functionality to your PostgreSQL instances, allowing you to use enhanced features in your database on top of the existing PostgreSQL capabilities.Cloud SQL for PostgreSQL has added support for more than ten extensions this year, allowing our customers to leverage the benefits of Cloud SQL managed databases along with the extensions built by the PostgreSQL community.We introduced support for these new extensions to enable access to foreign tables across instances using postgres_fdw, remove bloat from tables and indexes and optionally restore the physical order of clustered indexes (pg_repack), manage pages in memory from PostgreSQL (pgfincore), inspect the contents of database pages at a low level (pageinspect), examine the free space map, the visibility map and page-level visibility info using pg_freespacemap and pg_visibility, use a procedural language handler (PL/proxy) to allow remote procedural calls among PostgreSQL databases, and support postgresql-hll data type.Now, we’re adding extensions to support connectivity within databases and to support new data types that make it easier to store and query IP addresses and phone numbers.New extension: dblinkdblink functionality is complementary to the cross-database connectivity capabilities we introduced earlier this year as PL/Proxy and postgres_fdw extensions. Depending on your database architecture, you might come across situations when you need to query data outside of your application’s database or query the same database with an independent transaction (autonomous) within a local transaction. Dblink allows you to query remote databases and provide you more flexibility and better connectivity in your environment.You can use dblink as part of a SELECT statement for every SQL statement that returns results. For repetitive queries and future use, we recommend creating a view to avoid multiple code modifications in case of changes in connection string or name info.With dblink available now, we still recommend in most use cases to keep the data you need to query under the same database and leverage schemas as possible due to complexity and performance overheads. Another alternative is to use the postgres_fdw extension for more transparency, standards compliance, and better performance.New data types: Ip4r and prefixInternet protocols IPv4 and IPv6 are both commonly used today; IPv4 is Internet Protocol Version 4, while IPv6 is the next generation of Internet Protocol allowing a broader range of IP addresses. IPv6 was introduced in 1998 with the purpose of replacing IPv4.Ip4r allows you to use six data types to store IPv4 and IPv6 addresses and address ranges. These data types provide better functionality and performance than the built-in inet and cidr data types. These data types can leverage PostgreSQL’s capabilities such as primary key, unique key, b-tree index, constraints, etc.prefix data type supports phone number prefixes, allowing customers with call centers and phone systems who are interested in routing calls and matching phone numbers and operators to store prefix data easily and perform operations efficiently. With prefix extension available, you can use prefix_range data type for table and index creation, cast function and query the table with the following operators: <=, <, =, <>, >=, >, @>, <@, &&, |, &Try out the new extensionsdblink, Ip4r and prefix extensions are now available for you to use along with the eight other supported extensions on Cloud SQL for PostgreSQL. Learn more about PostgreSQL extensions and what’s available.Related ArticlePreparing your MySQL database for migration with Database Migration ServiceRecently, we announced the new Database Migration Service (DMS) to make it easier to migrate databases to Google Cloud. DMS is an easy-to…Read Article
Quelle: Google Cloud Platform

da Agency

Most popular public datasets to enrich your BigQuery analyses

From rice genomes to historical hurricane data, Google Cloud Public Datasets offer a world of exploration and insight. The more than 20 PB across 200+ datasets in our Public Dataset Program helps you explore big data and data analytics without a lot of cost, setup, or overhead. You can explore up to 1 TB per month at no cost, and you don’t even need a billing account to start using BigQuery sandbox. Joining public datasets with your own data gets you insights right away, such as adding location data for better transportation management or incorporating NOAA’s climate data into forecasting models. Retailers can use census demographics for market analysis, and analysts at those companies can map users with census block, zip code, and county boundary geometries.These datasets can help you start exploring and layering data points, and they also make data analytics a lot easier for enterprise customers. These utility datasets let you start with a set of valid, clean data, rather than having to start from scratch.You can access Google Cloud’s public datasets through BigQuery and Cloud Storage using either legacy or standard SQL queries. Researchers can also use BigQuery MLto train advanced machine learning models with this data right inside BigQuery at no additional cost. BigQuery GIS provides convenient, built-in capabilities to ingest, process, and analyze geospatial data when you want a location component in your data analysis.Here, we’ll explore some common datasets and how they’re used. Expanding access to data for healthcare and research. This year, COVID-19 public datasets have been incredibly important to researchers looking to understand and combat the virus. As the pandemic began in March, we announced an initial set of free public datasets to help researchers, data scientists, and analysts combat the coronavirus. These include the COVID-19 Open Data dataset, the Global Health Data from the World Bank, and OpenStreetMap data. The COVID-19 datasets are free to access and query through September 15, 2021. Looker customers can also install the COVID-19 block, which includes the Community Mobility Data Block, from the Marketplace, where they can accelerate their analyses of the public datasets using curated explore environments and purposeful dashboards. Anyone can go ahead and access the dashboards and explore environments here. The Looker Demographic data block contains demographic information from the American Community Survey.Building the right tools to bring COVID-19 data to all. Google Cloud and partner SADA also collaborated earlier this year on building the National Response Portal, an open data platform that combines multiple datasets for an on-the-ground view of the pandemic. The Oklahoma State Department of Health and governor’s office used COVID-19 public datasets and Looker data blocks to build a dashboard on the state website to monitor cases and update residents.Layering weather, climate, and GIS datasets for a better understanding of nature. Weather and climate are popular datasets to explore. Within BigQuery, you can explore climate simulation data from a collaboration with the Lamont-Doherty Earth Observatory of Columbia University and the Pangeo Project. In addition, the World Climate Research Programme released the Coupled Model Intercomparison Project Phase 6 (CMIP6) data archive. This dataset will be continuously updated and may eventually contain 20 PB of data. Other climate-related datasets include those from NOAA on lightning and hurricanes, and Looker’s Weather data block that contains daily weather reporting in the United States at the zip code level from 1920 until now. You can see how GlideFinder built a platform that ingests satellite data to monitor wildfires, using data characteristics like temperature. And here’s how to use a Colab notebook to analyze data on daily temperature readings from around the world. In Looker, users can leverage the weather block to analyze weather data and join it back onto their own data sources to get an entire picture of how climate may be impacting their business.Using genomics data to improve food security. Our rice genome dataset derives from the Rice 3K dataset, which analyzes genetic variation, population structure, and diversity among more than 3,000 diverse Asian cultivated rice genomes. Our researchers then used DeepVariant to re-analyze that dataset with the goal of improving food security by speeding up genetic enhancement to increase rice crop yield. Get to know cryptocurrencies using blockchain datasets. Our Public Datasets Program includes a set of cryptocurrency blockchain datasets, so you can start to better understand this modern concept. The datasets consist of the blockchain transaction history of Bitcoin and Ethereum, plus others, and you’ll also find a set of queries and views to enable multi-chain meta analysis and integration with conventional financial record processing systems. Putting public datasets to useWe’re always interested to hear all the ways that analysts and researchers use public datasets to further understanding of so many different causes and topics. 2020 has brought fascinating, hopeful stories of how data has helped fight COVID-19, including our COVID-specific datasets and other public health datasets. Google Cloud has been able to help with COVID-19 academic research by offering high-performance compute and other technology resources along with public datasets. One important note is that the contents of these datasets are provided to the public strictly for educational and research purposes only. We are not onboarding or managing PHI or PII data as part of our COVID-19 public datasets. Google has practices and policies in place to ensure that data is handled in accordance with widely recognized patient privacy and data security policies.What will you do with public datasets on BigQuery? Dive into the BigQuery sandbox to get started. Have an idea for a dataset? Add it to our request tracker.Learn more: Get started with geospatial data exploration in this beginner’s guide to BigQuery GIS.Explore Looker’s blocks here and request a demo to learn moreSee how a cross-industry team of AI practitioners ramped up data use to fight COVID.Check out the latest Kaggle competitions to test your skills.Related ArticleCOVID-19 public dataset program: Making data freely accessible for better public outcomesExplore valuable public health data related to COVID-19 with free public datasets, available in Google Cloud’s BigQueryRead Article
Quelle: Google Cloud Platform