Loading...

verticapy.read_avro

verticapy.read_avro(path: str, schema: str | None = None, table_name: str | None = None, usecols: list | None = None, new_name: dict | None = None, insert: bool = False, reject_on_materialized_type_error: bool = False, flatten_maps: bool = True, flatten_arrays: bool = False, temporary_table: bool = False, temporary_local_table: bool = True, gen_tmp_table_name: bool = True, ingest_local: bool = True, genSQL: bool = False, materialize: bool = True, use_complex_dt: bool = False) → vDataFrame

Ingests an AVRO file using flex tables.

Parameters

path: str

Absolute path where the AVRO file is located.

schema: str, optional

Schema where the AVRO file will be ingested.

table_name: str, optional

Final relation name.

usecols: list, optional

list of the AVRO parameters to ingest. The other ones will be ignored. If empty, all the AVRO parameters will be ingested.

new_name: dict, optional

Dictionary of the new column names. If the AVRO file is nested, it is recommended to change the final names because special characters will be included in the new column names. For example, {"param": {"age": 3, "name": Badr}, "date": 1993-03-11} will create 3 columns: “param.age”, “param.name” and “date”. You can rename these columns using the new_name parameter with the following dictionary: {"param.age": "age", "param.name": "name"}

insert: bool, optional

If set to True, the data will be ingested to the input relation. The AVRO parameters must be the same as the input relation otherwise they will not be ingested. If set to True, table_name cannot be empty.

reject_on_materialized_type_error: bool, optional

boolean, whether to reject a data row that contains a materialized column value that cannot be coerced into a compatible data type. If the value is False and the type cannot be coerced, the parser sets the value in that column to None. If the column is a strongly-typed complex type, as opposed to a flexible complex type, then a type mismatch anywhere in the complex type causes the entire column to be treated as a mismatch. The parser does not partially load complex types.

flatten_maps: bool, optional

boolean, whether to flatten sub-maps within the AVRO data, separating map levels with a period (.). This value affects all data in the load, including nested maps.

flatten_arrays: bool, optional

boolean, whether to convert lists to sub-maps with integer keys. When lists are flattened, key names are concatenated in the same way as maps. lists are not flattened by default. This value affects all data in the load, including nested lists.

temporary_table: bool, optional

If set to True, a temporary table will be created.

temporary_local_table: bool, optional

If set to True, a temporary local table will be created. The parameter schema must be empty, otherwise this parameter is ignored.

gen_tmp_table_name: bool, optional

Sets the name of the temporary table. This parameter is only used when the parameter temporary_local_table is set to True and if the parameters table_name and schema are unspecified.

ingest_local: bool, optional

If set to True, the file will be ingested from the local machine.

genSQL: bool, optional

If set to True, the SQL code for creating the final table is generated but not executed. This is a good way to change the final relation types or to customize the data ingestion.

materialize: bool, optional

If set to True, the flex table is materialized into a table. Otherwise, it will remain a flex table. Flex tables simplify the data ingestion but have worse performace compared to regular tables.

use_complex_dt: bool, optional

boolean, whether the input data file has complex structure. If set to True, most of the other parameters are ignored.

Returns

vDataFrame

The vDataFrame of the relation.

Examples

In this example, we will first download an AVRO file and then ingest it into Vertica database.

We import verticapy:

import verticapy as vp

Hint

By assigning an alias to verticapy, we mitigate the risk of code collisions with other libraries. This precaution is necessary because verticapy uses commonly known function names like “average” and “median”, which can potentially lead to naming conflicts. The use of an alias ensures that the functions from verticapy are used as intended without interfering with functions from other libraries.

Let’s download the AVRO file.

import requests

url = "https://github.com/vertica/VerticaPy/raw/master/verticapy/tests/utilities/variants.avro"

r = requests.get(url)

open('variants.avro', 'wb').write(r.content)
Out[5]: 1952604

Let’s ingest the AVRO file into the Vertica database.

from verticapy.core.parsers.avro import read_avro

read_avro(
    path = "variants.avro",
    table_name = "variants",
    schema = "public",
)
Abc
strand
Varchar(20)
Abc
annotation.reference
Varchar(32)
Abc
annotation.geneTraitAssociation
Long varchar(40)
Abc
Long varchar(560)
Abc
Long varchar(8090)
Abc
annotation.ancestralAllele
Varchar(20)
Abc
annotation.additionalAttributes
Varchar(20)
Abc
type
Varchar(20)
Abc
sv
Varchar(20)
123
start
Integer
Abc
names
Long varchar(40)
Abc
Varchar(20)
Abc
annotation.minorAlleleFreq
Varchar(20)
Abc
annotation.minorAllele
Varchar(20)
Abc
annotation.id
Varchar(20)
Abc
annotation.hgvs
Varchar(20)
Abc
annotation.geneExpression
Varchar(20)
Abc
annotation.geneDrugInteraction
Long varchar(40)
Abc
alternate
Varchar(20)
Abc
Long varchar(162500)
123
end
Integer
Abc
Long varchar(1155)
123
annotation.start
Integer
Abc
Varchar(20)
123
annotation.chromosome
Integer
Abc
annotation.alternate
Varchar(20)
Abc
annotation.__name__
Varchar(34)
Abc
reference
Varchar(32)
123
length
Integer
Abc
id
Varchar(22)
123
chromosome
Integer
Abc
annotation.populationFrequencies
Long varchar(40)
Abc
Long varchar(800)
Abc
__name__
Varchar(22)
1+A{}[null][null]SNP[null]16053770{}[null][null][null][null][null]{}T160537701605377022TVariantAnnotationA1rs58768083622{}VariantAvro
2+A{}[null][null]SNP[null]16053945{}[null][null][null][null][null]{}G160539451605394522GVariantAnnotationA1rs58763912822{}VariantAvro
3+A{}[null][null]SNP[null]16054671{}[null][null][null][null][null]{}T160546711605467122TVariantAnnotationA1rs18879377722{}VariantAvro
4+A{}[null][null]INDEL[null]16051723{}[null][null][null][null][null]{}160517231605172322VariantAnnotationA1rs20190622422{}VariantAvro
5+A{}[null][null]SNP[null]16055879{}[null][null][null][null][null]{}T160558791605587922TVariantAnnotationA1rs58773700922{}VariantAvro
6+A{}[null][null]INDEL[null]16057193{}[null][null][null][null][null]{}160571931605719322VariantAnnotationA1rs58768921022{}VariantAvro
7+A{}[null][null]SNP[null]16057127{}[null][null][null][null][null]{}T160571271605712722TVariantAnnotationA1rs58768507622{}VariantAvro
8+A{}[null][null]SNP[null]16058478{}[null][null][null][null][null]{}C160584781605847822CVariantAnnotationA1rs58772340822{}VariantAvro
9+A{}[null][null]SNP[null]16050075{}[null][null][null][null][null]{}G160500751605007522GVariantAnnotationA1rs58769762222{}VariantAvro
10+A{}[null][null]SNP[null]16058989{}[null][null][null][null][null]{}G160589891605898922GVariantAnnotationA1rs58774065922{}VariantAvro
11+A{}[null][null]SNP[null]16050783{}[null][null][null][null][null]{}G160507831605078322GVariantAnnotationA1rs58774356822{}VariantAvro
12+A{}[null][null]SNP[null]16059796{}[null][null][null][null][null]{}C160597961605979622CVariantAnnotationA1rs58766154222{}VariantAvro
13+A{}[null][null]SNP[null]16051771{}[null][null][null][null][null]{}T160517711605177122TVariantAnnotationA1rs58765058322{}VariantAvro
14+A{}[null][null]SNP[null]16062118{}[null][null][null][null][null]{}G160621181606211822GVariantAnnotationA1rs58769696822{}VariantAvro
15+A{}[null][null]SNP[null]16051796{}[null][null][null][null][null]{}G160517961605179622GVariantAnnotationA1rs58777252722{}VariantAvro
16+A{}[null][null]SNP[null]16062617{}[null][null][null][null][null]{}C160626171606261722CVariantAnnotationA1rs58761719522{}VariantAvro
17+A{}[null][null]SNP[null]16052576{}[null][null][null][null][null]{}G160525761605257622GVariantAnnotationA1rs58759623722{}VariantAvro
18+A{}[null][null]SNP[null]16067552{}[null][null][null][null][null]{}G160675521606755222GVariantAnnotationA1rs58760595822{}VariantAvro
19+A{}[null][null]SNP[null]16053725{}[null][null][null][null][null]{}T160537251605372522TVariantAnnotationA1rs58769908222{}VariantAvro
20+A{}[null][null]SNP[null]16069419{}[null][null][null][null][null]{}T160694191606941922TVariantAnnotationA1rs58770763022{}VariantAvro
21+A{}[null][null]SNP[null]16069584{}[null][null][null][null][null]{}T160695841606958422TVariantAnnotationA1rs58762571222{}VariantAvro
22+A{}[null][null]SNP[null]16053819{}[null][null][null][null][null]{}G160538191605381922GVariantAnnotationA1rs58762834722{}VariantAvro
23+A{}[null][null]SNP[null]16069716{}[null][null][null][null][null]{}C160697161606971622CVariantAnnotationA1rs58777342622{}VariantAvro
24+A{}[null][null]SNP[null]16054329{}[null][null][null][null][null]{}T160543291605432922TVariantAnnotationA1rs58769723522{}VariantAvro
25+A{}[null][null]SNP[null]16070916{}[null][null][null][null][null]{}G160709161607091622GVariantAnnotationA1rs58760943422{}VariantAvro
26+A{}[null][null]SNP[null]16054889{}[null][null][null][null][null]{}T160548891605488922TVariantAnnotationA1rs58765493322{}VariantAvro
27+A{}[null][null]SNP[null]16071011{}[null][null][null][null][null]{}G160710111607101122GVariantAnnotationA1rs58771657922{}VariantAvro
28+A{}[null][null]SNP[null]16055581{}[null][null][null][null][null]{}T160555811605558122TVariantAnnotationA1rs58767101822{}VariantAvro
29+A{}[null][null]SNP[null]16077355{}[null][null][null][null][null]{}T160773551607735522TVariantAnnotationA1rs55990118622{}VariantAvro
30+A{}[null][null]SNP[null]16060354{}[null][null][null][null][null]{}T160603541606035422TVariantAnnotationA1rs18188388922{}VariantAvro
31+A{}[null][null]SNP[null]16116029{}[null][null][null][null][null]{}T161160291611602922TVariantAnnotationA1rs54662431722{}VariantAvro
32+A{}[null][null]SNP[null]16061453{}[null][null][null][null][null]{}G160614531606145322GVariantAnnotationA1rs58774753422{}VariantAvro
33+A{}[null][null]SNP[null]16116636{}[null][null][null][null][null]{}T161166361611663622TVariantAnnotationA1rs53635630122{}VariantAvro
34+A{}[null][null]SNP[null]16062517{}[null][null][null][null][null]{}G160625171606251722GVariantAnnotationA1rs58772282022{}VariantAvro
35+A{}[null][null]SNP[null]16117705{}[null][null][null][null][null]{}G161177051611770522GVariantAnnotationA1rs57793070622{}VariantAvro
36+A{}[null][null]SNP[null]16066066{}[null][null][null][null][null]{}C160660661606606622CVariantAnnotationA1rs58776690422{}VariantAvro
37+A{}[null][null]SNP[null]16140116{}[null][null][null][null][null]{}T161401161614011622TVariantAnnotationA1rs57133309822{}VariantAvro
38+A{}[null][null]SNP[null]16071205{}[null][null][null][null][null]{}G160712051607120522GVariantAnnotationA1rs58776973222{}VariantAvro
39+A{}[null][null]SNP[null]16140171{}[null][null][null][null][null]{}C161401711614017122CVariantAnnotationA1rs57571330522{}VariantAvro
40+A{}[null][null]SNP[null]16073985{}[null][null][null][null][null]{}T160739851607398522TVariantAnnotationA1rs57324433222{}VariantAvro
41+A{}[null][null]SNP[null]16141729{}[null][null][null][null][null]{}T161417291614172922TVariantAnnotationA1rs55068960422{}VariantAvro
42+A{}[null][null]SNP[null]16080463{}[null][null][null][null][null]{}T160804631608046322TVariantAnnotationA1rs57695325022{}VariantAvro
43+A{}[null][null]SNP[null]16142295{}[null][null][null][null][null]{}C161422951614229522CVariantAnnotationA1rs57440529122{}VariantAvro
44+A{}[null][null]SNP[null]16116373{}[null][null][null][null][null]{}G161163731611637322GVariantAnnotationA1rs53218007022{}VariantAvro
45+AA{}[null][null]INDEL[null]16063482{}[null][null][null][null][null]{}160634831606348222VariantAnnotationAA2rs58770050422{}VariantAvro
46+A{}[null][null]SNP[null]16117033{}[null][null][null][null][null]{}G161170331611703322GVariantAnnotationA1rs56780636322{}VariantAvro
47+AAAGCCAGAACCACTC{}[null][null]INDEL[null]16052395{}[null][null][null][null][null]{}160524101605239522VariantAnnotationAAAGCCAGAACCACTC16rs58777403022{}VariantAvro
48+A{}[null][null]SNP[null]16119328{}[null][null][null][null][null]{}T161193281611932822TVariantAnnotationA1rs55054959622{}VariantAvro
49+C{}[null][null]INDEL[null]16066472{}[null][null][null][null][null]{}160664721606647222VariantAnnotationC1rs58766904022{}VariantAvro
50+A{}[null][null]SNP[null]16140141{}[null][null][null][null][null]{}T161401411614014122TVariantAnnotationA1rs53872918122{}VariantAvro
51+C{}[null][null]SNP[null]16050213{}[null][null][null][null][null]{}T160502131605021322TVariantAnnotationC1rs58765492122{}VariantAvro
52+A{}[null][null]SNP[null]16140367{}[null][null][null][null][null]{}T161403671614036722TVariantAnnotationA1rs55475699222{}VariantAvro
53+C{}[null][null]SNP[null]16050319{}[null][null][null][null][null]{}T160503191605031922TVariantAnnotationC1rs58771227522{}VariantAvro
54+A{}[null][null]SNP[null]16140711{}[null][null][null][null][null]{}T161407111614071122TVariantAnnotationA1rs52980088522{}VariantAvro
55+C{}[null][null]SNP[null]16050527{}[null][null][null][null][null]{}A160505271605052722AVariantAnnotationC1rs58776943422{}VariantAvro
56+A{}[null][null]SNP[null]16143629{}[null][null][null][null][null]{}G161436291614362922GVariantAnnotationA1rs56272387622{}VariantAvro
57+C{}[null][null]SNP[null]16050688{}[null][null][null][null][null]{}T160506881605068822TVariantAnnotationC1rs58775619122{}VariantAvro
58+A{}[null][null]SNP[null]16144314{}[null][null][null][null][null]{}T161443141614431422TVariantAnnotationA1rs53538071122{}VariantAvro
59+C{}[null][null]SNP[null]16050840{}[null][null][null][null][null]{}G160508401605084022GVariantAnnotationC1rs58761682222{}VariantAvro
60+A{}[null][null]SNP[null]16144887{}[null][null][null][null][null]{}C161448871614488722CVariantAnnotationA1rs52778666122{}VariantAvro
61+A{}[null][null]SNP[null]16145180{}[null][null][null][null][null]{}G161451801614518022GVariantAnnotationA1rs55334792222{}VariantAvro
62+C{}[null][null]SNP[null]16050568{}[null][null][null][null][null]{}A160505681605056822AVariantAnnotationC1rs58763889322{}VariantAvro
63+C{}[null][null]SNP[null]16050732{}[null][null][null][null][null]{}T160507321605073222TVariantAnnotationC1rs58765203322{}VariantAvro
64+C{}[null][null]SNP[null]16051165{}[null][null][null][null][null]{}T160511651605116522TVariantAnnotationC1rs58773179822{}VariantAvro
65+{}[null][null]INDEL[null]16061832{}[null][null][null][null][null]{}A160618321606183222AVariantAnnotation1rs58771479222{}VariantAvro
66+A{}[null][null]INDEL[null]16050740{}[null][null][null][null][null]{}160507401605074022VariantAnnotationA1rs58774723122{}VariantAvro
67+A{}[null][null]SNP[null]16050958{}[null][null][null][null][null]{}T160509581605095822TVariantAnnotationA1rs58763680722{}VariantAvro
68+A{}[null][null]SNP[null]16051874{}[null][null][null][null][null]{}T160518741605187422TVariantAnnotationA1rs58773147322{}VariantAvro
69+A{}[null][null]SNP[null]16055428{}[null][null][null][null][null]{}T160554281605542822TVariantAnnotationA1rs58768355722{}VariantAvro
70+A{}[null][null]SNP[null]16058070{}[null][null][null][null][null]{}G160580701605807022GVariantAnnotationA1rs284323822{}VariantAvro
71+A{}[null][null]SNP[null]16059063{}[null][null][null][null][null]{}C160590631605906322CVariantAnnotationA1rs58764403422{}VariantAvro
72+A{}[null][null]SNP[null]16061312{}[null][null][null][null][null]{}G160613121606131222GVariantAnnotationA1rs58772852022{}VariantAvro
73+A{}[null][null]SNP[null]16061702{}[null][null][null][null][null]{}G160617021606170222GVariantAnnotationA1rs58765018922{}VariantAvro
74+A{}[null][null]SNP[null]16062290{}[null][null][null][null][null]{}T160622901606229022TVariantAnnotationA1rs58769368322{}VariantAvro
75+A{}[null][null]SNP[null]16066845{}[null][null][null][null][null]{}T160668451606684522TVariantAnnotationA1rs58768270322{}VariantAvro
76+A{}[null][null]SNP[null]16066908{}[null][null][null][null][null]{}G160669081606690822GVariantAnnotationA1rs58763563122{}VariantAvro
77+A{}[null][null]SNP[null]16069731{}[null][null][null][null][null]{}G160697311606973122GVariantAnnotationA1rs58765128722{}VariantAvro
78+A{}[null][null]SNP[null]16069791{}[null][null][null][null][null]{}C160697911606979122CVariantAnnotationA1rs58771779622{}VariantAvro
79+A{}[null][null]SNP[null]16078604{}[null][null][null][null][null]{}G160786041607860422GVariantAnnotationA1rs57178923822{}VariantAvro
80+A{}[null][null]SNP[null]16088912{}[null][null][null][null][null]{}C160889121608891222CVariantAnnotationA1rs53026106322{}VariantAvro
81+A{}[null][null]SNP[null]16117709{}[null][null][null][null][null]{}C161177091611770922CVariantAnnotationA1rs55693762022{}VariantAvro
82+{}[null][null]INDEL[null]16085566{}[null][null][null][null][null]{}TTTC160855691608556622TTTCVariantAnnotation4rs56110016122{}VariantAvro
83+{}[null][null]INDEL[null]16141583{}[null][null][null][null][null]{}T161415831614158322TVariantAnnotation1rs54513269522{}VariantAvro
84+A{}[null][null]SNP[null]16051432{}[null][null][null][null][null]{}G160514321605143222GVariantAnnotationA1rs58767205622{}VariantAvro
85+A{}[null][null]SNP[null]16051927{}[null][null][null][null][null]{}T160519271605192722TVariantAnnotationA1rs58772489522{}VariantAvro
86+A{}[null][null]SNP[null]16052492{}[null][null][null][null][null]{}C160524921605249222CVariantAnnotationA1rs58771972922{}VariantAvro
87+A{}[null][null]SNP[null]16054888{}[null][null][null][null][null]{}G160548881605488822GVariantAnnotationA1rs58775762522{}VariantAvro
88+A{}[null][null]SNP[null]16055641{}[null][null][null][null][null]{}G160556411605564122GVariantAnnotationA1rs58770888322{}VariantAvro
89+A{}[null][null]SNP[null]16057563{}[null][null][null][null][null]{}G160575631605756322GVariantAnnotationA1rs284489222{}VariantAvro
90+A{}[null][null]SNP[null]16058342{}[null][null][null][null][null]{}T160583421605834222TVariantAnnotationA1rs58763191922{}VariantAvro
91+A{}[null][null]SNP[null]16058491{}[null][null][null][null][null]{}C160584911605849122CVariantAnnotationA1rs58767857122{}VariantAvro
92+A{}[null][null]SNP[null]16059670{}[null][null][null][null][null]{}G160596701605967022GVariantAnnotationA1rs58760130322{}VariantAvro
93+A{}[null][null]SNP[null]16060010{}[null][null][null][null][null]{}G160600101606001022GVariantAnnotationA1rs58775585922{}VariantAvro
94+A{}[null][null]SNP[null]16064108{}[null][null][null][null][null]{}T160641081606410822TVariantAnnotationA1rs58767954722{}VariantAvro
95+A{}[null][null]SNP[null]16064763{}[null][null][null][null][null]{}T160647631606476322TVariantAnnotationA1rs58773987522{}VariantAvro
96+A{}[null][null]SNP[null]16069044{}[null][null][null][null][null]{}G160690441606904422GVariantAnnotationA1rs58762213922{}VariantAvro
97+A{}[null][null]SNP[null]16069088{}[null][null][null][null][null]{}G160690881606908822GVariantAnnotationA1rs58775772022{}VariantAvro
98+A{}[null][null]SNP[null]16069611{}[null][null][null][null][null]{}C160696111606961122CVariantAnnotationA1rs58767887022{}VariantAvro
99+A{}[null][null]SNP[null]16070616{}[null][null][null][null][null]{}G160706161607061622GVariantAnnotationA1rs58765436922{}VariantAvro
100+A{}[null][null]SNP[null]16070737{}[null][null][null][null][null]{}T160707371607073722TVariantAnnotationA1rs58770829022{}VariantAvro
Rows: 1-100 | Columns: 34

Let’s ingest only two columns.

read_avro(
    path = "variants.avro",
    table_name = "variants_usecols",
    schema = "public",
    usecols  = [
        "type",
        "sv",
    ],
)
Abc
type
Varchar(20)
Abc
sv
Varchar(20)
1INDEL[null]
2INDEL[null]
3INDEL[null]
4INDEL[null]
5INDEL[null]
6INDEL[null]
7INDEL[null]
8INDEL[null]
9INDEL[null]
10INDEL[null]
11INDEL[null]
12INDEL[null]
13INDEL[null]
14INDEL[null]
15INDEL[null]
16INDEL[null]
17INDEL[null]
18INDEL[null]
19SNV[null]
20SNV[null]
21SNV[null]
22SNV[null]
23SNV[null]
24SNV[null]
25SNP[null]
26SNP[null]
27SNP[null]
28SNP[null]
29SNP[null]
30SNP[null]
31SNP[null]
32SNP[null]
33SNP[null]
34SNP[null]
35SNP[null]
36SNP[null]
37SNP[null]
38SNP[null]
39SNP[null]
40SNP[null]
41SNP[null]
42SNP[null]
43SNP[null]
44SNP[null]
45SNP[null]
46SNP[null]
47SNP[null]
48SNP[null]
49SNP[null]
50SNP[null]
51SNP[null]
52SNP[null]
53SNP[null]
54SNP[null]
55SNP[null]
56SNP[null]
57SNP[null]
58SNP[null]
59SNP[null]
60SNP[null]
61SNP[null]
62SNP[null]
63SNP[null]
64SNP[null]
65SNP[null]
66SNP[null]
67SNP[null]
68SNP[null]
69SNP[null]
70SNP[null]
71SNP[null]
72SNP[null]
73SNP[null]
74SNP[null]
75SNP[null]
76SNP[null]
77SNP[null]
78SNP[null]
79SNP[null]
80SNP[null]
81SNP[null]
82SNP[null]
83SNP[null]
84SNP[null]
85SNP[null]
86SNP[null]
87SNP[null]
88SNP[null]
89SNP[null]
90SNP[null]
91SNP[null]
92SNP[null]
93SNP[null]
94SNP[null]
95SNP[null]
96SNP[null]
97SNP[null]
98SNP[null]
99SNP[null]
100SNP[null]
Rows: 1-100 | Columns: 2

Note

You can ingest multiple AVRO files into the Vertica database by using the following syntax.

read_avro(
    path = "*.avro",
    table_name = "variants_multi_files",
    schema = "public",
)

See also

read_csv() : Ingests a CSV file into the Vertica DB.
read_file() : Ingests an input file into the Vertica DB.
read_json() : Ingests a JSON file into the Vertica DB.
read_pandas() : Ingests the pandas.DataFrame into the Vertica DB.